MATCH AGAINST (multiple columns)

[slj90]

I have a query that searches a single column

SELECT * FROM allmovies
        WHERE MATCH (all_title)
        AGAINST ('shrek' IN NATURAL LANGUAGE MODE);

However, I want to be able to search multiple columns

SELECT * FROM allmovies
        WHERE MATCH (all_title, all_tags, all_directors)
        AGAINST ('speilberg' IN NATURAL LANGUAGE MODE);

But that receives the error

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

It works when searching each column individually, like above.

What is wrong with my query?

Thanks,

[Barand]

The columns in the MATCH() must match the columns in the index definition.

You could either
 

...WHERE MATCH MATCH (all_title) AGAINST ('shrek' IN NATURAL LANGUAGE MODE) OR
         MATCH MATCH (all_tags) AGAINST ('shrek' IN NATURAL LANGUAGE MODE) OR
         MATCH MATCH (all_directors) AGAINST ('shrek' IN NATURAL LANGUAGE MODE) 

or

add a fulltext text index on (all_title, all_tags, all_directors)