0

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.

Nanny Boy
  • 107
  • 6

1 Answers1

1

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`);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks, it's works adding FULLTEXT index together. However I wonder how to add FULLTEXT in Join table if I want `foto.file` as part of search column as well? – Nanny Boy Mar 01 '17 at 07:36
  • You can't define any type of index that spans multiple tables. You have to define one index in each table, and use one MATCH() call per table. See example in this answer of mine from back in 2009! http://stackoverflow.com/questions/1241602/mysql-match-across-multiple-tables/1241739#1241739 – Bill Karwin Mar 01 '17 at 14:54