mysql - 优化 JOIN 子查询中的 WHERE 子查询

Question

SELECT journey.departure AS startTime, departure.duration AS seconds, ADDTIME(journey.departure, departure.duration) AS departureTime, p.section
FROM journey
JOIN journey_day ON journey_day.journey = journey.id
JOIN pattern p ON p.id = journey.pattern
JOIN (
    SELECT departure.section, SEC_TO_TIME(SUM(time)) AS duration
    FROM pattern_link departure
    WHERE departure.from_sequence < (SELECT from_sequence FROM pattern_link WHERE from_stop = "1980SN12532B" AND section = departure.section)
    GROUP BY departure.section
) departure ON departure.section = p.section
WHERE journey.service = "44-A-A-y10-1" AND p.direction = "inbound" AND journey_day.day = 4
GROUP BY journey.id
ORDER BY departure

这个查询需要很长时间才能运行。这是因为 JOIN 子查询内的 WHERE 子句中的子查询所致。问题是，我不知道还能怎么写。在出发连接的 WHERE 语句中，我需要知道 from_sequence 编号(基本上是一个 ID)，而我能做到这一点的唯一方法是根据中的条件进行查询WHERE 子句。

有什么想法吗？

Answer 1

您的版本将受益于 pattern_link(section, from_stop, time) 上的索引。该索引可能是改进查询的最简单方法。

您还可以尝试使用聚合将其编写为显式join:

SELECT departure.section, SEC_TO_TIME(SUM(time)) AS duration
FROM pattern_link departure JOIN
     (SELECT section, from_sequence
      FROM pattern_link
      WHERE from_stop = '1980SN12532B'
     ) s
     ON s.section = departure.section
WHERE departure.from_sequence < s.from_sequence
GROUP BY departure.section