execute...too long耗时30秒的mysql查询
execute...too long耗时30秒的mysql查询
提问于 2010-08-17 23:42:00
我试图在一个网站上运行一个MySQL查询作为搜索，但这需要很长的时间。
我直接从phpMyAdmin上运行它，花了大约20秒才完成。
下面是我正在运行的查询：
SELECT * , bb_business.id AS bid
FROM bb_business
LEFT OUTER JOIN bb_offers ON bb_business.id = bb_offers.store_id
LEFT OUTER JOIN bb_cat_business ON bb_business.id = bb_cat_business.store_id
LEFT OUTER JOIN bb_categories ON bb_categories.id = bb_cat_business.cat_id
WHERE bb_business.active = '1'
AND (
bb_business.business_name LIKE '%altas%'
OR bb_categories.category_name LIKE '%altas%'
OR bb_business.business_name LIKE '%juice%'
OR bb_categories.category_name LIKE '%juice%'
)
AND (
bb_business.city LIKE '%%'
OR bb_business.state LIKE '%%'
OR bb_business.zip LIKE '%%'
)
GROUP BY bb_business.business_name
ORDER BY bb_business.business_name DESC
LIMIT 1 , 10`速度:显示0-0行(总共1行，查询耗时20.1130秒)
我不确定向你展示结构的最好方式。但这里是一些表的排序的复制和粘贴：
bb_ach 128 InnoDB latin1_swedish_ci 32.0 KB -
bb_business 2,862 InnoDB latin1_swedish_ci 1.6 MB -
bb_categories 15 MyISAM utf8_general_ci 6.7 KB 60 Bytes
bb_cat_business 2,926 MyISAM utf8_general_ci 49.6 KB 17 Bytes
bb_emails 13 MyISAM utf8_general_ci 6.0 KB -
bb_member 409 MyISAM utf8_general_ci 62.7 KB -
bb_member_card 593 MyISAM utf8_general_ci 112.1 KB -
bb_merchant 154 InnoDB latin1_swedish_ci 32.0 KB -
bb_offers 80 InnoDB latin1_swedish_ci 16.0 KB -
bb_products 2 InnoDB latin1_swedish_ci 32.0 KB 如果我能提供任何其他东西。请让我知道。
非常感谢你的帮助。
EDIT##################################
好的，使用OMG Ponies的建议，我修改了一些东西。制作了一些MyISAM，并创建了一些全文索引。
我以这个查询结束：
SELECT *, bb_business.id AS bid FROM bb_business
LEFT OUTER JOIN bb_offers ON bb_business.id = bb_offers.store_id
LEFT OUTER JOIN bb_cat_business ON bb_business.id = bb_cat_business.store_id
LEFT OUTER JOIN bb_categories ON bb_categories.id = bb_cat_business.cat_id
WHERE bb_business.active = '1' AND
MATCH(bb_business.business_name) AGAINST ('".addslashes($business)."') OR
MATCH(bb_categories.category_name) AGAINST ('".addslashes($business)."') OR
MATCH (bb_business.city,bb_business.state,bb_business.zip) AGAINST ('".addslashes($city)."')
GROUP BY bb_business.business_name
ORDER BY bb_business.imported,".$this->sortCol." ".$this->sortDirection." LIMIT ".$this->start.",".$this->limit我认为这让它变得更慢了;)
解释是这样的：
id select_type table type possible_keys key key_len ref rows Extra
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 因此，结果是第一个查询需要40秒才能完成。如果我再次搜索完全相同的短语，速度与我期望的MYSQL执行的速度相同。以毫秒计。这只是第一次搜索，需要永远的时间...
还有什么想法吗？我很乐意花钱请人帮忙。
Stack Overflow用户
回答已采纳
发布于 2010-10-06 22:01:14
我只想回来看看这个，然后给出一个答案。我最终创建了一个索引表，并创建了一个PHP脚本来索引主数据库。归根结底，MySQL在处理多个表之间的多个匹配方面做得不好。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3504263
复制相关文章
相似问题
腾讯云开发者
