58

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.

Aurelio De Rosa
  • 21,856
  • 8
  • 48
  • 71
LucasRolff
  • 1,228
  • 3
  • 11
  • 20

5 Answers5

137

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.

jerrymouse
  • 16,964
  • 16
  • 76
  • 97
  • 14
    @Zerpex if your problem solved then you should mark it as ans – vikas Feb 13 '13 at 08:49
  • @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? – vikas Feb 13 '13 at 09:11
  • 3
    Note that if you are doing match() against multiple columns, you have to have full text index covering exactly same fields. – dsomnus Mar 09 '15 at 15:56
  • This was helpful.. especially having the same number of columns in the same order.. – David Addoteye Sep 07 '16 at 08:05
  • 6
    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 – Ben Jan 22 '17 at 17:03
  • 1
    @jerrymouse That last line saved my night. – Gopal Sharma Sep 10 '18 at 18:42
  • 1
    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() – Tarquin Oct 26 '18 at 21:00
  • @jerrymouse, is there any way to use spring boot annotation to do this (Altering Table) ? – Nilupul Heshan Jun 02 '21 at 19:49
19

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)
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Chogo
  • 311
  • 3
  • 11
9

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

Toby
  • 9,696
  • 16
  • 68
  • 132
karthic
  • 101
  • 1
  • 1
2

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;
jorr-el
  • 136
  • 1
  • 5
1

Make sure the table engine is set to MyISAM.

naturaljoin
  • 475
  • 2
  • 7