Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I've got a MySQL query somewhat like the following:

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

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

#1210 - Incorrect arguments to MATCH

If I only MATCH one column it doesn't error and works correctly, but as soon as I try to MATCH multiple columns it fails with this error. I'm running MySQL 5.0.45 and the MySQL 5.0 Full-Text Search Functions documentation implies I can do this.

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

Update @Zak: I can't post the table creation statements, but I can say that in this example the columns are as follows: products.name, descriptions.overview, descriptions.specs. If I specify them in full table.column format, it doesn't change the behavior.

However, if I lose products.name from the 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.

like image 223
morgant Avatar asked Nov 29 '22 19:11

morgant


2 Answers

The arguments to MATCH() must be the same columns in your FULLTEXT index definition. They must be the same in number as well as position.

Also, since you can't make an index with multiple columns from different tables, you can't match against columns from different tables in a single MATCH() call.

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

like image 94
Bill Karwin Avatar answered Dec 06 '22 16:12

Bill Karwin


I've run into that same problem. The thing is that you can't match columns from different tables so you have 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 it helps!

like image 23
Frankie Avatar answered Dec 06 '22 16:12

Frankie