为什么我的 MySQL 查询这么慢？
Background:
entities tables currently has 14,111 records
articles table currently has 5211 records
我正在尝试查找所有有效（已完成）且实体为“google”的文章
# 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'
查询花费如此长的时间可能会出现什么问题？
我想补充一点，数据透视表中的两个字段都已建立索引。
提前感谢您的帮助
更新
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
Background:
entities tables currently has 14,111 records
articles table currently has 5211 records
I am trying to find all articles that are active (completed) and have the entity 'google'
# 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'
What might be the issue with the query taking so long?
I would add that both fields in the pivot table are indexed.
Thanks in advance for you help
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Entity.strict 或 Article.state 未编入索引。
在 SELECT 语句之前使用 EXPLAIN 并检查哪些表正在被完全扫描。这将暗示什么需要索引。
Entity.strict or Article.state are not indexed.
Use EXPLAIN before your SELECT statement and check which tables are being fully scanned. That will hint what needs indexing.
你真的需要左连接吗？！恕我直言，如果没有它，你的查询应该会得到提升；）
do you really need the left join?! imho your query should get a boost without it ;)