Can't find FULLTEXT index matching the column list (indexes is set)

Solution 1:

Assuming you are using MyISAM engine, Execute:

ALTER TABLE products ADD FULLTEXT(brand);

The fulltext index should contain exactly the same number of columns, in same order as mentioned in MATCH clause.

Solution 2:

If you don't feel like having the columns in the same order as in the match clause( or the same number), you can always use 'OR' that is:

ALTER TABLE products ADD FULLTEXT(brand);
ALTER TABLE products ADD FULLTEXT(product_name);

 SELECT * FROM products WHERE MATCH(brand) AGAINST('+Skoda*' IN BOOLEAN MODE) OR MATCH(product_name) AGAINST('+productName*' IN BOOLEAN MODE)

Solution 3:

When everything was right and still got this error I found that the KEYS were disabled. A simple error that is sometimes overlooked:

Make sure you have enabled the keys on that table.

It didn't work for me when I had disabled the keys. But when I enabled the keys ALTER TABLE table name ENABLE KEYS; it worked fine

Solution 4:

I found I also needed to do this on my instance as the index was not visible. It was a checkbox while exploring MySQL Workbench. While invisible the index is not reachable by a query.

ALTER TABLE products ALTER INDEX brand VISIBLE;