Why am I getting \"Can\'t find FULLTEXT index matching the column list\" when searching multiple columns?

Troubleshooting "Can't find FULLTEXT index matching the column list" for Multiple Column Search

When constructing a MATCH...AGAINST query involving multiple columns in a fulltext index, it's crucial to ensure that the column order in the index matches the order in which they appear in the MATCH clause. Based on the error message you've encountered, it seems your fulltext index definition includes more columns than your MATCH clause.

As you mentioned, your table contains a fulltext index defined on multiple columns:

FULLTEXT KEY `name` (`name`,`breadcrumb`,`description`,`brand`,`price`,`year`,`km`,`usage`,`type`)

However, your MATCH clause only includes the following column:

MATCH(`brand`) AGAINST('Skoda');

This mismatch between the number of columns in the index and the MATCH clause is causing the error. To rectify this issue, you need to adjust either the index definition or the MATCH clause to ensure they align.

The recommended approach is to modify the fulltext index to match the columns you intend to use in your search. In this case, you can execute the following query to add a new fulltext index that includes only the brand column:

ALTER TABLE products ADD FULLTEXT(brand);

After adding the proper index, rerun your MATCH query:

SELECT * FROM products WHERE MATCH(`brand`) AGAINST('Skoda');

This should now return the expected results without the error message.

The above is the detailed content of Why am I getting \"Can\'t find FULLTEXT index matching the column list\" when searching multiple columns?. For more information, please follow other related articles on the PHP Chinese website!