Why is my MySQL MATCH () AGAINST () query failing with columns from LEFT JOIN'd databases?

Question

I have a MySQL query something like this:

SELECT * FROM products LEFT JOIN descriptions ON products.DescriptionID = descriptions.ID WHERE MATCH (name, overview, specs) AGAINST ('ram'); 

All the columns I'm trying to execute using MATCH() AGAINST() are FULLTEXT, but when testing in phpMyAdmin I get the following error:

 #1210 - Incorrect arguments to MATCH 

If I only MATCH one column, it does not work with an error and works correctly, but as soon as I try to execute MATCH several columns, this error does not occur. I am using MySQL 5.0.45, and MySQL 5.0 Full-text search functions imply that I can do this.

Is it because of the LEFT JOIN ? Do I need OR together with a bunch of function calls MATCH() AGAINST() ?

Update @Zak: I cannot post table creation instructions, but I can say that in this example the columns look like this: products.name , descriptions.overview , descriptions.specs . If I specify them in full table.column format, this will not change the behavior.

However, if I lose products.name from MATCH() , I get the following error:

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

But both descriptions.overview and descriptions.specs are FULLTEXT.

Answer 1

MATCH() arguments must be the same columns in the FULLTEXT index definition. They should be the same in quantity as well as in position.

Also, since you cannot make an index with multiple columns from different tables, you cannot match columns from different tables with a single MATCH() call.

If you want to search for text columns from different tables, you will need to create a FULLTEXT index in each table, and then search for each index with a separate call to MATCH() . Use OR expressions to combine results.

Answer 2

I ran into the same problem. The thing is, you cannot match columns from different tables, so you need to split the query.

Try something like this (edited to match columns and tables):

 SELECT p.name, d.overview, d.specs FROM products AS p LEFT JOIN descriptions AS d ON p.DescriptionID = d.ID WHERE MATCH (p.name) AGAINST ('ram') OR MATCH (d.overview, d.specs) AGAINST ('ram'); 

Hope this helps!