2

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

Raggaer
  • 3,244
  • 8
  • 36
  • 67

1 Answers1

1

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64