MySQL“或MATCH”挂起（非常慢）在多个表上 [英] MySQL "OR MATCH" hangs (very slow) on multiple tables

问题描述

在学习如何进行MySQL全文搜索之后，针对多个表的推荐解决方案是或MATCH ，然后执行其他数据库调用。您可以在下面的查询中看到。

当我这样做时，它只会处于繁忙状态，而我无法访问MySQL数据库。

 选择
 a.`product_id`，a.`name`，a.`lug`，a.`描述'，b.`list_price`，b.`price`，c.`image`，c.`swatch`，e.`name` AS industry，
 MATCH（a.`name`，a.` （'％s'IN BOOLEAN模式）与相关性
 FROM 
`products`作为一个左加入`website_products`由于b 
 ON（a。sku`，a.`description`）AGAINST 'product_id` = b.`product_id`）
 LEFT JOIN（SELECT`product_id`，`image`，`swatch` FROM`product_images` WHERE`sequence` = 0）AS c 
 ON（a。 `product_id` = c.`product_id`）
 LEFT JOIN`brands` AS d 
 ON（a.`brand_id` = d.`brand_id`）
 INNER JOIN`industries` AS e ON（a.`industry_id` = e.`industry_id`）
 WHERE 
 b.`website_id` =％d 
 AND b.`status` =％d 
 AND b 。'active` =％d 
 AND MATCH（a.`name`，a.`sku`，a.`description`）AGAINST（'％s'IN BOOLEAN模式）
或MATCH（d.```）AGAINST（'BOOLEAN模式下的'％s'）
 GROUP BY a.`product_id` 
 ORDER BY相关性DESC 
 LIMIT 0，9 
  

任何帮助都将不胜感激。

---

编辑

所有涉及的表格都是MyISAM，utf8_general_ci。

以下是EXPLAIN SELECT语句：

  id select_type表类型possible_keys key key_len参考行额外
 1 PRIMARY a ALL NULL NULL NULL NULL 16076使用临时;使用filesort 
 1 PRIMARY b ref product_id product_id 4 database.a.product_id 2 
 1 PRIMARY e eq_ref PRIMARY PRIMARY 4 database.a.industry_id 1 
 1 PRIMARY< derived2> ALL NULL NULL NULL NULL 23261 
 1 PRIMARY d eq_ref PRIMARY PRIMARY 4 database.a.brand_id 1使用where 
 2 DERIVED product_images ALL NULL NULL NULL NULL 25933使用where 
  
 
 
 
我不知道如何让它看起来整洁 - 对不起 
 
 
 
 
 
更新
 
 
 
它在196秒后返回查询（我认为是正确的）。没有多个表的查询需要大约.56秒（我知道它非常慢，我们计划在不久的将来更改为solr或sphinx），但是196秒?? b 
 $ b 
如果我们可以在品牌名称中添加一个数字（d。 name ），这也可以工作
解决方案
为了回答第一个问题，需要使用圆括号来解决这个问题。
  围绕整个匹配反对或匹配反对：
  WHERE 
 b.`website_id` =％d 
 AND b.`status` =％d 
 AND b.`active` =％d 
 AND（
 MATCH（a.`name`，a.`sku`，a。 （'％s'IN BOOLEAN模式）
或MATCH（d.```）AGAINST（BOOLEAN模式中的'％s'）
）
  
我不明白如何使用 EXPLAIN SELECT ，但它帮助了很多位，所以谢谢你！这将第一个数字16076行减少到143个。然后我注意到另外两个行数超过23和25000行。 
  LEFT JOIN（SELECT`product_id`，`image`，`swatch` FROM`product_images` WHERE`sequence` = 0）AS c 
 ON（a.`product_id` = c.`product_id`）
  
我之所以这样做是有原因的，然后发生了变化。当我改变它时，我没有意识到我可以做一个正常的 LEFT JOIN ：
  LEFT JOIN`product_images` as c 
 ON（a.`product_id` = c.`product_id`）
  
 
 
 
 
 
 
这使得我的最终查询如下所示:(而且速度更快，从196秒变为0.0084左右）
 
 
  SELECT 
 a.`product_id`，a.`name`，a.`slug`，a.`description`，b.`list_price` ，b.`price`，
 c.`image`，c.`swatch`，e.`name` AS industry，
 MATCH（a.`name`，a.`sku`，a （'％s'IN BOOLEAN模式）与相关性
 FROM 
`products`作为左加入`website_products`由于b 
 ON（a.`product_id` = b.`product_id`）
 LEFT JOIN`product_images` AS c 
 ON（a.`product_id` = c.`product_id`）
 LEFT JOIN`品牌`AS d 
 ON（a.`brand_id` = d.`brand_id`）
 INNER JOIN`industries` AS e 
 ON（a.`industry_id` = e.`industry _id`）
 WHERE 
 b.`website_id` =％d 
 AND b.`status` =％d 
 AND b.`active` =％d 
 AND c.`sequence` =％d 
 AND（
 MATCH（a.`name`，a.`sku`，a.`description`）AGAINST（'BOOLEAN MODE'中的'％s'）
或MATCH（d.```）AGAINST（BOOLEAN模式中的'％s'）
）
 GROUP BY a.`product_id` 
 ORDER BY相关性DESC 
限制0，9 
  
哦，甚至在我用多个表格进行全文搜索之前，它大约需要1/2秒。这是大大改善。
 
After learning how to do MySQL Full-Text search, the recommended solution for multiple tables was OR MATCH and then do the other database call. You can see that in my query below.

When I do this, it just gets stuck in a "busy" state, and I can't access the MySQL database.
SELECT 
 a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, b.`price`, c.`image`, c.`swatch`, e.`name` AS industry, 
 MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE )     AS relevance 
FROM 
 `products` AS a LEFT JOIN `website_products` AS b 
  ON (a.`product_id` = b.`product_id`) 
 LEFT JOIN ( SELECT `product_id`, `image`, `swatch` FROM `product_images` WHERE `sequence` = 0) AS c 
  ON (a.`product_id` = c.`product_id`) 
 LEFT JOIN `brands` AS d 
  ON (a.`brand_id` = d.`brand_id`) 
 INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`) 
WHERE 
 b.`website_id` = %d
 AND b.`status` = %d
 AND b.`active` = %d 
 AND MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) 
  OR MATCH ( d.`name` ) AGAINST ( '%s' IN BOOLEAN MODE )
GROUP BY a.`product_id` 
ORDER BY relevance DESC 
LIMIT 0, 9 
Any help would be greatly appreciated.



EDIT

All the tables involved are  MyISAM, utf8_general_ci.

Here's the EXPLAIN SELECT statement:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 16076 Using temporary; Using filesort
1 PRIMARY b ref product_id product_id 4 database.a.product_id 2  
1 PRIMARY e eq_ref PRIMARY PRIMARY 4 database.a.industry_id 1  
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 23261  
1 PRIMARY d eq_ref PRIMARY PRIMARY 4 database.a.brand_id 1 Using where
2 DERIVED product_images ALL NULL NULL NULL NULL 25933 Using where
I don't know how to make that look neater -- sorry about that



UPDATE

it returns the query after 196 seconds (I think correctly). The query without multiple tables takes about .56 seconds (which I know is really slow, we plan on changing to solr or sphinx soon), but 196 seconds??

If we could add a number to the relevance if it was in the brand name ( d.name ), that would also work
解决方案
I found 2 things slowing down my query drastically and fixed them.

To answer the first problem, it needed parentheses around the entire "MATCH AGAINST OR MATCH AGAINST":
WHERE 
    b.`website_id` = %d
    AND b.`status` = %d
    AND b.`active` = %d 
    AND ( 
        MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) 
        OR MATCH ( d.`name` ) AGAINST ( '%s' IN BOOLEAN MODE )
    )
I didn't understand how to use EXPLAIN SELECT, but it helped quite a bit, so thank you! This reduced that first number 16076 rows to 143. I then noticed the other two with over 23 and 25 thousand rows. That was cause from this line:
LEFT JOIN ( SELECT `product_id`, `image`, `swatch` FROM `product_images` WHERE `sequence` = 0) AS c 
    ON (a.`product_id` = c.`product_id`)
There was a reason I was doing this in the first place, which then changed. When I changed it, I didn't realize I could do a normal LEFT JOIN:
LEFT JOIN `product_images` AS c 
    ON (a.`product_id` = c.`product_id`)




This makes my final query like this: (and MUCH faster went from the 196 seconds to 0.0084 or so)
SELECT 
    a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, b.`price`, 
    c.`image`, c.`swatch`, e.`name` AS industry, 
    MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) AS relevance 
FROM 
    `products` AS a LEFT JOIN `website_products` AS b 
        ON (a.`product_id` = b.`product_id`) 
    LEFT JOIN `product_images` AS c 
        ON (a.`product_id` = c.`product_id`) 
    LEFT JOIN `brands` AS d 
        ON (a.`brand_id` = d.`brand_id`) 
    INNER JOIN `industries` AS e 
        ON (a.`industry_id` = e.`industry_id`) 
WHERE 
    b.`website_id` = %d
    AND b.`status` = %d
    AND b.`active` = %d
    AND c.`sequence` = %d
    AND ( 
        MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) 
        OR MATCH( d.`name` ) AGAINST( '%s' IN BOOLEAN MODE ) 
    )
GROUP BY a.`product_id` 
ORDER BY relevance DESC 
LIMIT 0, 9
Oh, and even before I was doing a full text search with multiple tables, it was taking about 1/2 a second. This is much improved.

                        
这篇关于MySQL“或MATCH”挂起（非常慢）在多个表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！