Innodb: Can't find FULLTEXT index matching the

2020-07-06 07:04发布

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?

4条回答
我命由我不由天
2楼-- · 2020-07-06 07:14

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`);
查看更多
狗以群分
3楼-- · 2020-07-06 07:20

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')
查看更多
霸刀☆藐视天下
4楼-- · 2020-07-06 07:33
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" );
查看更多
贼婆χ
5楼-- · 2020-07-06 07:40

You need a third index:

FULLTEXT(item_title, item_description)
查看更多
登录 后发表回答