CODEWITHSUNDEEP
mysql
RD Ward
RD Ward

Reputation: 6757

MATCH AGAINST with multiple columns in MySQL

I am trying to create a simplified search box that will match multiple columns against a keyword or keywords the user inputs. The following code is my attempt at using MySQL's MATCH/AGAINST. However, I cannot get the query to execute properly when there are two columns specified, in this case 'topic, country'. It will execute when I run the code as either 'country' or 'topic', but not both.

Is there a secret to this?

SELECT * ,
MATCH (
topic, country
)
AGAINST (
'China'
) AS score
FROM reports2
WHERE MATCH (
topic, country
)
AGAINST (
'China'
)
ORDER BY score DESC

yields:

#1191 - Can't find FULLTEXT index matching the column list

Which I find to be an inaccurate description of the error, because there are FULLTEXT indices for both of these. I even copied the table, turned it into MyISAM from INNODB, in order to do so.

Any suggestions would be appreciated.

Upvotes: 1

Views: 5461

Answers (1)

knittl
knittl

Reputation: 265966

You don't need fulltext indices for each column, but a single fulltext index covering both columns

FULLTEXT_INDEX(topic, country)

Having a single index on each column will not work

FULLTEXT(topic); FULLTEXT(country); /* will not work as expected */

I also think that order is important, but I could be mistaken in that regard

Upvotes: 7

Related Questions