为什么我的MySQL查询很慢?
问题描述:
Background:
entities tables currently has 14,111 records
articles table currently has 5211 records
我想找到是有效的(完成)的所有文章,并有实体“谷歌”为什么我的MySQL查询很慢?
# Finding articles that have the entity google takes:
# 4 ms
SELECT `Article`.`id` FROM `articles_entities` AS `ArticlesEntity`
LEFT JOIN `entities` AS `Entity` ON (`ArticlesEntity`.`entity_id` = `Entity`.`id`)
WHERE `Entity`.`strict` = 'google'
# Finding articles that have the entity google and is active takes:
# 1800 ms
SELECT `Article`.`id` FROM `articles_entities` AS `ArticlesEntity`
LEFT JOIN `entities` AS `Entity` ON (`ArticlesEntity`.`entity_id` = `Entity`.`id`)
LEFT JOIN `articles` AS `Article` ON (`ArticlesEntity`.`article_id` = `Article`.`id`)
WHERE `Entity`.`strict` = 'google' AND `Article`.`state` = 'completed'
什么可能是与查询需要如此长的问题?
我想补充一点,数据透视表中的两个字段都是索引的。
在此先感谢您的帮助
UPDATE
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Entity ref PRIMARY,strict strict 767 const 1 Using where
1 SIMPLE ArticlesEntity ref article_id,entity_id,article_id_2 entity_id 108 b2b.Entity.id 4
1 SIMPLE Article eq_ref PRIMARY,state PRIMARY 108 b2b.ArticlesEntity.article_id 1 Using where
答
你真的需要左连接吗?!恕我直言,你的查询应该得到一个提升没有它;)
查询返回多少行? – dkretz 2010-06-23 17:52:06