mysql - 重新审视 MySQL 多重匹配查询......需要很长时间

Question

我正在修改几天前的问题。重写了我的查询。

有人可以告诉我为什么使用我将在下面发布的查询在多个表上运行任何类型的查询都需要这么长时间吗？

有人有时间帮我解决这个问题吗？我可以支付 200 美元，我认为这对于两个小时的工作来说已经足够了。我会将答案发布回此处，以便对每个人都有好处。

查询基本上是这样的:

SELECT bb_business.business_name, bb_business.id AS bid FROM bb_business
LEFT JOIN bb_offers       ON bb_business.id   = bb_offers.store_id
LEFT JOIN bb_cat_business ON bb_business.id   = bb_cat_business.store_id
LEFT JOIN bb_categories   ON bb_categories.id = bb_cat_business.cat_id
WHERE bb_business.active = '1'
  AND MATCH(bb_business.business_name)   AGAINST ('zebra')
  OR  MATCH(bb_categories.category_name) AGAINST ('zebra')
  OR  MATCH (bb_business.city,bb_business.state,bb_business.zip) AGAINST ('zebra')
GROUP BY bb_business.business_name
ORDER BY bb_business.business_name DESC
LIMIT 1,10

第一次执行该查询需要 50 秒。第二次的速度如预期的那样快。

如果我将该查询更改为仅使用一个“匹配”，速度会很快。一旦我添加第二个 MATCH 或 LIKE 语句，执行时间就会恢复到 40-60 次。

运行该精确查询运行于:MySQL 返回一个空结果集(即零行)。 (查询耗时 47.7614 秒)

解释返回此:

1 SIMPLE bb_business ALL NULL NULL NULL NULL 2877 Using temporary; Using filesort 
1 SIMPLE bb_offers ALL NULL NULL NULL NULL 94   
1 SIMPLE bb_cat_business ALL NULL NULL NULL NULL 5697   
1 SIMPLE bb_categories eq_ref PRIMARY PRIMARY 8 buxback_site.bb_cat_business.cat_id 1 Using where 

当仅使用一个匹配项时，查询将使用我在 bb_business 上的全文索引。当我有多个匹配项时，它似乎没有使用任何索引。

以下是 bb_business 上的索引:

PRIMARY  PRIMARY 2877       id  
store_id  UNIQUE 2877       store_id  
index_business_name  INDEX 2877       business_name  
business_name  FULLTEXT 1       business_name  
city  FULLTEXT 1       city  
state  
zip  

以下是 bb_categories 的索引:

PRIMARY  PRIMARY 15       id  
category_name  UNIQUE None       category_name  
category_name_2  FULLTEXT None       category_name 

我很绝望!

谢谢!

Answer 1

问题在于您强制 MySQL 创建临时表来解决查询。只要您进行多场比赛，这几乎就会成为一个问题。

如果您的用例确实需要您这样做，那么您有两个主要选项(我可以看到)。

1. 创建一个非规范化的汇总表，您可以在其中将全文索引放在需要匹配的所有列上。然后，保持此表更新(通过触发器、存储过程，或者只是在代码中执行查询时尽职调查)...然后针对该汇总表进行所有匹配(它应该消除对多个 MATCH 子句的需要)在一个查询中)...
2. 另一种选择是使用专用搜索引擎，例如 Apache SOLR或Sphinx 。您需要单独搜索，然后从数据库中拉回结果，但是这些搜索引擎比 MySQL 强大得多(好吧，不强大，但更容易执行复杂查询)，并且执行这些查询的速度比 MySQL 快得多。他们可以这样做，因为他们预先索引了一切(他们牺牲了磁盘空间 - 有时是很多 - 为了速度)...