Innodb: Can't find FULLTEXT index matching the column list when queried more than 1 columns

Question

I'm trying to run a very simple query on my MySQL INNODB table:

SELECT * 
FROM items 
WHERE MATCH (item_title,item_description) AGAINST ('dog')

Both column item_title and item_description have a FULLTEXT index.

I keep getting this error:

Can't find FULLTEXT index matching the column list

My issue: when I query just item_title or just item_description then it works fine. But when I do both at once in 1 query, as shown above, I get that error.

Any idea what is wrong?

Expressly

Probably duplicate question, here – Cantu

That is regarding multiple tables. I'm just querying one table. Just 2 columns. So that doesn't help me unfortunately. – Expressly

Answer 1

You need a third index:

FULLTEXT(item_title, item_description)

Emileemilee

please, I have the same problem, can you improve it with example ? thanks – Mangum

@GabrieleCarbonai - apparently our problem has some difference. So, start your own question, be sure to include your particulars, plus any differences. – Emileemilee

Answer 2

I might be late, just post here in case of someone get confuse :)

Run this query:

ALTER TABLE `items` ADD FULLTEXT(`item_title`,`item_description`);

Then you are able to run full-text search:

SELECT * 
FROM items 
WHERE MATCH (item_title,item_description) AGAINST ('dog')

Assyrian

Answer 3

If you set full-text index for individual col that works. Say

ALTER TABLE `items` ADD FULLTEXT(`item_title`);
ALTER TABLE `items` ADD FULLTEXT(`item_description`);

Pyaemia

Answer 4

ALTER TABLE `table_name` ADD FULLTEXT (`item_title` ,`item_description`);
    and then ( "SELECT table_name.*,
    MATCH (item_title, item_description) AGAINST ('" . $search . "') AS relevance
    FROM table_name
    WHERE MATCH (item_title, item_description) AGAINST ('" . $search . "')
    ORDER BY relevance DESC" );

Phenomenalism

Answer 5

It will need one more index, combination of both column.

ALTER TABLE items ADD FULLTEXT INDEX title_desc_index (item_title, item_description);

After that you can run your query

SELECT * FROM items WHERE MATCH (item_title,item_description) AGAINST ('dog')

Gailgaile