0

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?

  • 1) Specify your MySQL version. 2) MATCH ... AGAINST in WHERE clause seems to be excess - all records from tested tables which do not match are already ejected by join condition. – Akina Jan 10 at 7:37
  • 1) mysql.exe Ver 8.0.12 for Win64 on x86_64 (MySQL Community Server - GPL). 2) ok, i got it. So now using MATCH ... AGAINST 2 times. Is that ok?! – SDeaV Jan 10 at 7:58
  • And which one will be best to use MATCH ... AGAINST in join or in WHERE clause? – SDeaV Jan 10 at 8:00
  • MySQL version allows you to try MATCH .. AGAINST filtering in CTE and joining in main query. I think the condition selectivity is good enough for filtered recordset to be compact. in join or in WHERE clause? In join only, or the logic will be changed. – Akina Jan 10 at 8:14
  • @Akina. Thanks for your response. How can I select an answer?! – SDeaV Jan 10 at 8:20
0

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.

  • thank you a lot. But I'm new in SQL queries. And to be fair I don't understand your Plan D. And little confused if I understand plan A. I like Plan B and Plan C. But it will look like denormalization of my database. Will it take much time in index'ing that table for every update? And I'm thinking of adding another synonyms column for each name column. It will be something like tags. – SDeaV Jan 12 at 18:03
  • I thought about joining all in one table. But there are two problems. 1) What if user selects specific brands or models?. I'll have to use brands ids in with OR. But i can add id of brand or model to 'new table' as brand_id. Then I can use this table without joining tables. 2) This is a biggest problem. Tables options, option_values and product_option_values. If user selects specific options values. Like screen_size = FHD or HD or just all available sizes. I think I'll have to use JOIN with OR. What do you think, @Rick James? – SDeaV Jan 12 at 20:01
  • @SDeaV - The Search will be the toughest problem you need to solve. Put aside the concepts of brands, options, etc, and focus on "what will the user want to search on?" and "How can I keep that simple and efficient?" Putting all the likely names, tags, model numbers, etc, into a single table with a single FULLTEXT index is probably the best. – Rick James Jan 13 at 1:39
  • @SDeaV - Indexes are automatically updated as you do updates/inserts/deletes. Just create the index and don't worry about it. – Rick James Jan 13 at 1:40
  • Thank you @Rick James. – SDeaV Jan 14 at 8:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.