左联接延迟查询5秒
左联接延迟查询5秒
提问于 2014-08-16 12:39:50
SELECT DISTINCT(journey.id), line.name, journey.departure
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.id
INNER JOIN service ON service.id = journey.service
INNER JOIN operator ON operator.id = service.operator
INNER JOIN line ON line.service = service.id
INNER JOIN pattern ON pattern.id = journey.pattern
INNER JOIN pattern_link pl ON pl.section = pattern.section AND pl.from_stop = "370023292"
INNER JOIN pattern_link pl2 ON pl2.section = pattern.section AND pl2.from_sequence < pl.from_sequence
WHERE journey_day.day = 1 AND CURDATE() BETWEEN service.date_start and service.date_end AND operator.id = "TMTL"
ORDER BY journey.departure上面是一个MySQL查询，运行时间大约为0.05秒。
一旦我附加了这个LEFT JOIN journey_code ON journey_code.journey = journey.id，它就会给查询再增加4-6秒。我觉得这与ON子句中对ON列的第二次引用有关。为什么这个LEFT JOIN会如此拖延查询结果？
有关journey_code表的信息：
id(INT 11 PK AI) | journey(VARCHAR 128) | code(VARCHAR 16)journey和code都有索引。我尝试了两个复合索引，但这并没有以任何方式加快查询速度。
添加到查询的EXPLAIN的结果：http://i.imgur.com/BVrm9qv.png
知道为什么会这样吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-08-16 12:55:50
尝试将上面的查询变成子查询：
SELECT j.*, jc.<whatever>
FROM (SELECT DISTINCT journey.id, line.name, journey.departure
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.id
INNER JOIN service ON service.id = journey.service
INNER JOIN operator ON operator.id = service.operator
INNER JOIN line ON line.service = service.id
INNER JOIN pattern ON pattern.id = journey.pattern
INNER JOIN pattern_link pl ON pl.section = pattern.section AND pl.from_stop = "370023292"
INNER JOIN pattern_link pl2 ON pl2.section = pattern.section AND pl2.from_sequence < pl.from_sequence
WHERE journey_day.day = 1 AND
CURDATE() BETWEEN service.date_start and service.date_end AND
operator.id = 'TMTL'
) j left join
journey_code jc
ON jc.journey = j.id
ORDER BY j.departure按照MySQL的工作方式，它应该运行子查询并实现它。如果在journey_code(journey)上有索引，那么联接应该是快速的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25340232
复制相关文章
相似问题
腾讯云开发者
