Using MATCH, AGAINST multiple times in MySQL fulltext search

Question

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?

• Akina: 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.
• sdeav: 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: And which one will be best to use MATCH ... AGAINST in join or in WHERE clause?
• Akina: 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.
• sdeav: @Akina. Thanks for your response. How can I select an answer?!

Answer 1

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.

• sdeav: 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: 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?
• Rick James: @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: @SDeaV - Indexes are automatically updated as you do updates/inserts/deletes. Just create the index and don't worry about it.
• sdeav: And one last question @RickJames. I did something like Plan C. I can get relevance of two colums seperately. But what I can use in WHERE clause? Should I use OR or there is somethin that i can do?