问题描述
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.
有什么想法为什么会这样?预先感谢.
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).
除了非常罕见的情况
这篇关于MySQL添加联接减慢整个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！