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

Question

I'm working with fulltext, I executed an command to add the fulltext index to multiple comments, and returned no errors, then I did:

SELECT * FROM products WHERE MATCH(`brand`) AGAINST('Skoda');

Which is in the brand column - but I get following:

Can't find FULLTEXT index matching the column list

Eventho, when my table looks like this:

FULLTEXT KEY `name` (`name`,`breadcrumb`,`description`,`brand`,`price`,`year`,`km`,`usage`,`type`)

Is it because I should use the name instead? to do the search? Or what can be wrong.

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;

View more solutions

Comments

[vikas]

@Zerpex if your problem solved then you should mark it as ans

[vikas]

@jerrymouse, I have a doubt, suppose I have three full text columns and I want to do full text search only on two columns, how I can?

[dsomnus]

Note that if you are doing match() against multiple columns, you have to have full text index covering exactly same fields.

[David Addoteye]

This was helpful.. especially having the same number of columns in the same order..

[Ben]

I found this answer in Jan 2017. It has a very important hint: exactly the same number of columns, in same order ... I havent seen this in the MySq dev pages. This seems true also for the innoDB engine. You saved my app

[Gopal Sharma]

@jerrymouse That last line saved my night.

[Tarquin]

This answer didnt directly help me as far as the answer to my problem, but it did explain my issue, thank you. Very important to take note that the index and the match clause needs to match, my PRIMARY index was effectively stopping this from working in a single MATCH()

[Lee Goddard]

Unnecessary: dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

[Jon Nezbit]

Thans a lot! The " IN BOOLEAN MODE" part saved my life!

[Nilupul Heshan]

@jerrymouse, is there any way to use spring boot annotation to do this (Altering Table) ?