MySQL添加join会降低整个查询速度

Question

SELECT journey.code, journey.departure, journey.end, group_concat(pattern_road.latitude)
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
LEFT JOIN pattern_road ON pattern_road.section = pattern.section
WHERE journey_day.day = 5 AND TIME(NOW()) BETWEEN journey.departure AND journey.end
GROUP BY journey.code

上面的查询大约需要100ms来执行。我对此很满意，但我现在需要添加另一个连接，当我尝试这样做时，查询速度会减慢到大约2秒。

这是带有额外联接的新的慢查询：

SELECT journey.code, journey.departure, journey.end, group_concat(pattern_road.latitude) AS road, group_concat(link.stop) AS stop
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
LEFT JOIN pattern_road ON pattern_road.section = pattern.section
INNER JOIN link ON link.section = pattern.section
WHERE journey_day.day = 5 AND TIME(NOW()) BETWEEN journey.departure AND journey.end
GROUP BY journey.code

注意事项：

额外的join也与前面的join (pattern_road) join操作在同一列上，我只能认为这一定是问题的原因。例如，如果我将pattern_road join替换为link join，查询返回到100ms，我只是不能同时使用这两个join并让它以100ms运行。

Database schematic/indexes in SQL Fiddle

你知道为什么会发生这种情况吗？提前谢谢。

Answer 1

你能试试这条路吗？

SELECT  SQL_NO_CACHE journey.code, journey.departure, journey.end,
  (select group_concat(pattern_road.latitude) from  pattern_road where pattern_road.section = pattern.section) AS road,
  (select group_concat(link.stop) from link where link.section = pattern.section) AS stop
FROM journey
INNER JOIN journey_day ON journey_day.journey = journey.code
INNER JOIN pattern ON pattern.code = journey.pattern
WHERE journey_day.day = 5 AND TIME(NOW()) BETWEEN journey.departure AND journey.end
GROUP BY journey.code