MySQL添加联接减慢整个查询 [英] MySQL adding join slows down whole query

问题描述

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秒.

The above query takes roughly 100ms to execute. I am happy with this, but I now need to add another join, when I try to do this the query slows down to roughly 2 seconds.

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

This is the new slow query with the extra join:

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.

The extra join also operates on the same column as the previous join (pattern_road) joins on, I can only think that this must be the cause of the problem. For example, if I replace pattern_road join with the link join, the query is back to 100ms, I just can't use both joins and have it run at 100ms.

SQL提琴中的数据库原理图/索引

有什么想法为什么会这样?预先感谢.

Any ideas why this is happening? Thanks in advance.

推荐答案

请记住，两列上的两个索引与一个不相同两列上的复合索引.一个查询只能在每个表中使用一个索引 ^* .

Remember that two indexes on two columns are not the same as one composite index on two columns. A query can only use one index per table^*.

在pattern(code, section)或pattern(section, code)上添加索引(需要对实际数据进行实时测试，以找出哪个提供更好的结果).

Add an index on pattern(code, section) or pattern(section, code) (a live test with the actual data is required to find out which one provides better result).

^* 除了 查看全文