Can't assign FULLTEXT index on my table

Question

I am trying to implement a search function on a database table using query expansion, and I'm aware that I have to add the FULLTEXT index on the fields I want to become searchable. I have tried to do this and got no errors from PHPMyAdmin, but when I examine the structure of the table the cardinality of the FULLTEXT index is 0. I have tried inidividually setting the FULLTEXT index and it seems to work, but my serach returns the error: Can't find FULLTEXT index matching the column list

Any ideas what's going wrong?

Answer 1

Check your database storage engine. InnoDB do not support FULLTEXT index. Change it to MyISAM

Answer 2

Try executing a REPAIR on the table you have just added them to, to (re)build the fulltext indexes:

REPAIR table foo

Also note that fulltext will only work on varchar or text columns. As a last resort, I would try restarting MySql.

The following page just about covers everything:

http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

Hope that helped.

Answer 3

I had to add the index on all fields i wanted to be searchable and in the combination I wanted to search them...