重新审视 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 快得多。他们可以这样做，因为他们预先索引了一切（他们牺牲了磁盘空间 - 有时是很多 - 为了速度）......