Home / Errors / Question No: 125

I want to searching data from my table using MySQL FULLTEXT search feature. So for the purpose when I attempt to run the below query, my MySQL Server returns the error #1191 - Can't find FULLTEXT index matching the column list.

SELECT id, title, category_id FROM books 
WHERE MATCH (title, detail, answer) 
      AGAINST ('What is mysql') LIMIT 0 , 30;
MySQL said:  #1191 - Can't find FULLTEXT index matching the column list

Could I know the reason that what part of my query causing the error #1191 - Can't find FULLTEXT index matching the column?

By your query, it seems that you are attempting to search the desired data using FULLTEXT INDEX functionality on NON-INDEXED columns of your table namely (title, detail, answer) that is why you are getting #1191 - Can't find FULLTEXT index matching the column error. To fix the problem, do one of the following things:

1. Add the FULLTEXT INDEX on the concerned column. You can do this simply by the following query:

ALTER TABLE books ADD FULLTEXT name_of_index(title,detail,answer);

2. Or change your query by adding "IN BOOLEAN MODE" because MySQL allows to search on FULLTEXT non-indexed columns in this mode. So you will have to do the following:

SELECT id, title, category_id FROM books 
WHERE
MATCH (title, detail, answer) 
 AGAINST ('What is mysql', IN BOOLEAN MODE)
LIMIT 0 , 30;