Optimize MySQL FULLTEXT searches to use another index

Question

I'm running a price comparison service, with a database of products that aims to become quite large (in the millions or more).

The Product table looks like this:

CREATE TABLE Product (
 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 site_id INT(10) UNSIGNED NOT NULL,
 brand VARCHAR(100) NULL,
 name VARCHAR(500) NULL,
 variation VARCHAR(100) NOT NULL,
 PRIMARY KEY (id),
 INDEX Product_Site (site_id),
 FULLTEXT INDEX Product_Fulltext (brand, name, variation),
)

Now I need to query products belonging to a given site, using a search string, which translates into this query:

SELECT * FROM Product
WHERE site_id = 7
AND MATCH (brand, name, variation) AGAINST('something')
ORDER BY MATCH (brand, name, variation) AGAINST('something') DESC
LIMIT 10;

The query is not very fast, even though my table is still quite small (< 1M).

The reason is given by EXPLAIN:

+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | Product | NULL | fulltext | Product_Site,Product_Fulltext | Product_Fulltext | 0 | const | 1 | 50.00 | Using where; Ft_hints: sorted |
+----+-------------+---------+------------+----------+-------------------------------+------------------+---------+-------+------+----------+-------------------------------+

It looks like MySQL is choosing to use the FULLTEXT index first, finding all rows that match the search string, then filter the rows that belong to the site I'm interested in. This is obviously highly inefficient.

And AFAIK:

• MySQL still cannot use two indexes in the same query,
• there is no way to have FULLTEXT act as a "hybrid" index that would be segmented by an integer id,
• partitioned tables do not support FULLTEXT indexes;

but I do hope that I'm missing something.

Is there any way to speed up this query? Or should I move away from MySQL to implement these searches? All suggestions welcome.

• Akina: How many records (average) matches site_id = 7 (or any another value)?
• BenMorel: Anywhere from 5,000 to 100,000.

Answer 1

Give this a try (and let us know if it helps):

SELECT p.*, ft.relevance
 FROM (
 SELECT id,
 MATCH (brand, name, variation) AGAINST('something' IN BOOLEAN MODE) AS relevance
 FROM Product
 WHERE MATCH (brand, name, variation) AGAINST('something' IN BOOLEAN MODE)
 ) AS ft
 JOIN Product AS p USING(id)
 WHERE p.side_id = 7
 ORDER BY ft.relevance DESC
 LIMIT 10;

Also, provide EXPLAIN FOMAT=JSON SELECT ....