在 MySQL 全文搜尋中多次使用 MATCH、AGAINST

我正在為我的電子商務項目使用 MySQL 數據庫。這是我的搜尋查詢：

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;

在這裡，我使用了 MATCH，AGAINST 3 次。首先在 SELECT 中獲取相關性。第二次在 JOIN 中，第三次在 WHERE 中。

1. 多次使用 MATCH, AGAINST 會影響速度嗎？

2）如果是，我該如何更改我的程式碼以獲得相同的結果並降低速度？

主要的性能問題來自OR多個表之間，然後來自需要檢查每個表中的行。

計劃一：

一個常見的改進是OR變成UNION，但它可能只是部分解決方案。看看這個查詢能走多遠（這將成為實際查詢中的子查詢）：

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

該查詢應該相當快，因為每個子查詢都可以有效地使用（我假設）存在於等中的FULLTEXT索引。此外，您必須擁有,等。brands``INDEX(brand_id)``INDEX(brand_model_id)

一旦看起來不錯（而且很快），其餘的會更加混亂，但速度很快。

B計劃：

從長遠來看，這可能會更好，但它需要設置。

將所有文本列建構為 5 列（所有 5 列都有一個FULLTEXT索引）。或者，您可以建構一個文本列，其中包含從其他列組合的所有文本。通過使用單列（帶有FULLTEXT），測試會更快，並且不需要ORor Coalesce。

該表將具有 的副本p.id，從而使您可以快速進入 5 個表以獲取 5 個個人MATCHes（如果尚未獲得）並進行ORDER BY計算。

計劃C：

這可能是最好的。注意格式

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

確實像 B 計劃，但有 2 個文本列——一個用於p.name，一個用於其他 4 個表的組合。您Prv從MATCH一列獲得，並且（大約）Brv + BMrv + Orv + OVrv從另一列獲得。

計劃 D：

嗯…這可能更簡單？有一個額外的表，其中包含p.id、name、 來源（產品、品牌等）和“重量”（100 或 1）。然後對該表進行一個簡單的查詢即可獲得幾乎所有需要的數據（p.name、p.price、p.created_at 除外）。然後JOIN得到products這 3 列。

引用自：https://dba.stackexchange.com/questions/226752