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

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

此查询绝对需要花一些时间才能运行。这是因为WHERE子查询内JOIN子句中的子查询。问题是，我不确定该如何写。在离开联接的WHERE语句中，我需要知道from_sequence编号（基本上是一个ID），唯一的方法是根据WHERE子句中的条件进行查询。

有任何想法吗？

最佳答案

您的版本将受益于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

关于mysql - 在JOIN子查询中优化WHERE子查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25201314/