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.
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.
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With