CODEWITHSUNDEEP
mysqlfull-text-searchfulltext-index
Raggaer
Raggaer

Reputation: 3318

MySQL FULLTEXT cant find index

I try to run the following query

SELECT * FROM complains WHERE match(title, description) against('+lorem' IN BOOLEAN MODE)

Table complains has two FULLTEXT indexes. title and description but I still get this error from MySQL

Error Code: 1191. Can't find FULLTEXT index matching the column list

However I can run the query for each row on its own

SELECT * FROM complains WHERE match(description) against('+lorem' IN BOOLEAN MODE)
SELECT * FROM complains WHERE match(title) against('+lorem' IN BOOLEAN MODE)

This works fine. What I am missing?

enter image description here

Upvotes: 2

Views: 839

Answers (1)

Shadow
Shadow

Reputation: 34284

If you are listing multiple fields in the match() operator and use Boolean mode and innodb table engine, then you need to have a multi-column fulltext index in place that covers the fields, as MySQL documentation on Boolean Full-Text Searches says:

InnoDB tables require a FULLTEXT index on all columns of the MATCH() expression to perform boolean queries. Boolean queries against a MyISAM search index can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

Upvotes: 1

Related Questions