CODEWITHSUNDEEP
mysqlfull-text-search
Nanny Boy
Nanny Boy

Reputation: 107

MySQL fulltext searches in InnoDB error

I have below query to perform multiple columns search:

SELECT 
    prod.pid, 
    prod.pname, 
    prod.description, 
    prod.status, 
    foto.set_cover, 
    foto.file
FROM products prod 
INNER JOIN second_child_categories secd ON prod.category_id = secd.second_id 
INNER JOIN photos foto ON prod.pid = foto.pid 
WHERE MATCH (prod.pname, prod.description)
AGAINST ('home')
AND foto.set_cover = '1' 
AND prod.status = '1' 
ORDER BY prod.created_date DESC

I use InnoDB engine and MySql Ver 14.14 Distrib 5.6.35

I added fulltext index in products table

ALTER TABLE `products` ADD FULLTEXT (`pname`);
ALTER TABLE `products` ADD FULLTEXT (`description`);

I run the query and get error:

#1191 - Can't find FULLTEXT index matching the column list

What's wrong with the query?

Thanks in advance.

Upvotes: 0

Views: 154

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

When you use MATCH() the columns you name must together be indexed as one fulltext index, not each column individually in a separate fulltext index.

Given the query you show, you need an index defined this way:

ALTER TABLE `products` ADD FULLTEXT (`pname`,`description`);

Upvotes: 1

Related Questions