Mysql – Using MATCH, AGAINST multiple times in MySQL fulltext search

full-text-searchgroup byjoin;MySQLorder-by

I'm using MySQL database for my e-commerce project.
And here is my query for search:

SELECT DISTINCT p.id, p.name, p.price, p.created_at,
    MATCH(p.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Prv,  
    MATCH(b.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Brv,   
    MATCH(bm.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as BMrv,  
    MATCH(o.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Orv,
    MATCH(ov.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as OVrv
FROM products p 
    LEFT JOIN brands b          ON  b.id = p.brand_id                     AND MATCH(b.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) 
    LEFT JOIN brand_models bm   ON  bm.id = p.brand_model_id              AND MATCH(bm.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN options o         ON  o.product_type_id = p.product_type_id AND MATCH(o.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) 
    LEFT JOIN product_option_values pov ON  pov.product_id = p.id 
    LEFT JOIN option_values ov  ON  ov.id = pov.option_value_id           AND MATCH(ov.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
WHERE   
    (MATCH(p.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)  
    OR MATCH(b.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)  
    OR MATCH(bm.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)  
    OR MATCH(o.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE) 
    OR MATCH(ov.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)) 
    AND 
    COALESCE(b.id, bm.id, o.id, ov.id, pov.id) IS NOT NULL
GROUP BY
    p.id
ORDER BY 
    ((Prv*100) + Brv + BMrv + Orv + OVrv) DESC, p.updated_at DESC;

Here I used MATCH, AGAINST 3 times. First in SELECT for relevance. Second in JOIN and third time in WHERE.

1) Can usage MATCH, AGAINST multiple times affect to speed?

2) If yes, how can I change my code to get same result and reduce speed?

Best Answer

The main performance problem comes from OR between multiple tables, then from the need to check for rows from each table.

Plan A:

One common improvement is to turn OR into UNION, but it may be only a partial solution. See how far you can get with this query (which will become a subquery in the real query):

SELECT p.id, MATCH(b...) AS Brv
      FROM brands b        JOIN products p ON b.id = p.brand_id
      WHERE MATCH(b...)
UNION ALL
SELECT p.id, MATCH(bm...) AS BMrv
      FROM brand_models bm JOIN products p ON bm.id = p.brand_model_id
      WHERE MATCH(bm...)
...

That query should be reasonably fast because each subquery can efficiently use the FULLTEXT index that (I assume) exists in brands, etc. Also, you must have INDEX(brand_id), INDEX(brand_model_id), etc.

Once that looks good (and fast), the rest will be even messier, but fast.

Plan B:

This is possibly better in the long run, but it requires setup.

Build a single table with all the text columns into 5 columns (with a single FULLTEXT index on all 5). Or, you could build a single text column with all the text combined from the other columns. By having a single column (with FULLTEXT), the test will be faster, and avoid the need for OR or Coalesce.

That table would have a copy of p.id, thereby allowing you to quickly reach into the 5 tables to get the 5 individual MATCHes (if not already gotten) and do the ORDER BY computation.

Plan C:

This might be best. Noticing the format of

((Prv*100) + Brv + BMrv + Orv + OVrv)

Do like Plan B, but with 2 text columns -- one for p.name, one for the combination of the other 4 tables. You get Prv from the MATCH on one column, and you get (approximately) Brv + BMrv + Orv + OVrv from the other text column.

Plan D:

Hmmm... This may be even simpler? Have an extra table with p.id, name, source (products, brands, etc) and a "weight" (100 or 1). Then a simple query against this table gets virtually all the data needed (except for p.name, p.price, p.created_at). Then JOIN to products to get those 3 columns.

Related Question