ShaneS — Tue, 02 Sep 2003 18:56:35 +0000

Well I see a few things here...

Not sure where you got the code, but I belive that is 4.0.X not 3.X code.

First off the create, I dont remeber seeing a "KEY" ever used, it is called an index used the INDEX namehere..

Quote: FULLTEXT

[index_name] (index_col_name,...)

Next we have your creation, you used a single column at a time. If you want them to be search together as you did in your query you must combined them like SO....

Quote: FULLTEXT INDEX namdesc (name,description)

Now for you search. MySQL 3.23.56 does not support boolean mode, only 4.0 and higher, so your search criteria can not have the "+" operators.

Now onto the problem...

The field list must be the FULLTEXT index name, if none is given you specify the fields in the proper order.

Quote: All parameters to the MATCH() function must be columns from the same table that is part of the same FULLTEXT index, unless the MATCH() is IN BOOLEAN MODE.

The MATCH() column list must exactly match the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE.

So you had 2 indexes requireing they each be matched seperatly. The documents are a little tough to follow, but because you gave indexes you Match would have have been Match(name) Against() AND Match(description) Against().

For this reason I suggest making index have a unique name not the same or too similar to a table's column name.

If you use my above Create fix, you can keep your existing query less the boolean operator. That will be the only way to search both field together as one FULLTEXT index.

Here is an example to help better uderstand how it works:
CREATE TABLE blank(foo tinytext, boo tinytext, zoo tinytext, doo tinytext, FULLTEXT INDEX fb(foo,boo), FULLTEXT INDEX zd(zoo,doo));

You select for fb can be MATCH(fb) or MATCH(foo,boo), one used the index, the other the columns that make the fulltext. Same applies for zd.

Hope that helps