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;