Mysql – Optimize MySQL FULLTEXT searches to use another index

full-text-searchindexMySQL

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.

Best Answer

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 ....

Related Question