mysql - MySQL添加联接减慢整个查询

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

上面的查询大约需要100毫秒才能执行。我对此感到满意，但是现在我需要添加另一个联接，当我尝试执行此操作时，查询速度将降低到大约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

注意事项：

额外的联接也与先前的联接（pattern_road）联接在同一列上运行，我只能认为这一定是问题的原因。例如，如果我将pattern_road join替换为link join，查询将返回100ms，那么我将无法同时使用两个联接并使其以100ms运行。

Database schematic/indexes in SQL Fiddle

任何想法为什么会这样？提前致谢。

最佳答案

你可以这样尝试吗？

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

关于mysql - MySQL添加联接减慢整个查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27666544/