CODEWITHSUNDEEP
mysqlsqljoinoptimizationsubquery
jskidd3
jskidd3

Reputation: 4783

Optimising WHERE subquery inside JOIN subquery

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

This query takes absolutely ages to run. This is because of the subquery in the WHERE clause inside the JOIN subquery. The problem is, I'm not sure how else to write it. In the WHERE statement in the departure join I need to know the from_sequence number (basically an ID) and the only way I can do that is by querying based on the criteria in the WHERE clause.

Any ideas?

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Your version would benefit from an index on pattern_link(section, from_stop, time). That index might be the easiest way to improve the query.

You can also try writing this as an explicit join by using aggregation:

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

Upvotes: 1

Related Questions