有人能告诉我为什么在多个表上运行任何类型的查询使用我将在下面发布的查询需要这么长时间吗?
有没有人有时间帮我解决这个问题?我可以付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秒。第二次它的速度很快。
如果我将查询改为只使用一个匹配项,那么速度很快。一旦我添加了第二个匹配或LIKE语句,它的执行次数就会恢复到40-60次。
运行精确的查询运行于:
MySQL returned an empty result set (i.e. zero rows). (Query took 47.7614 sec)
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
以下是BBU业务的索引:
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
以下是BBU类别的索引:
PRIMARY PRIMARY 15 id
category_name UNIQUE None category_name
category_name_2 FULLTEXT None category_name
我绝望了!
谢谢!
