MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Errors / Question No: 125

1191-Cant find FULLTEXT index matching the column list

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?

Answer No: 125

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;

Related MySQL FAQs to the Above FAQ

Why-I-see-an-error-when-I-EXPLAIN-for-update-query Why I see an error when I EXPLAIN for update query?

ERROR-1295-(HY000):-This-command-is-not-supported-in-the-prepared-statement-protocol-yet ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

ERROR-1064-(42000):-You-have-an-error-in-your-SQL-syntax ERROR 1064 (42000): You have an error in your SQL syntax

1264-Out-of-range-value-adjusted-for-column 1264 Out of range value adjusted for column

1048-Column-cannot-be-null 1048 Column cannot be null

1093-You-can-not-specify-target-table-comments-for-update-in-FROM-clause 1093 You can not specify target table comments for update in FROM clause

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.