title: Very slow MySQL query yet columns are properly indexed
body: <pre><code>SELECT DISTINCT journey.id, line.name AS line, getDestination(pattern.section) AS destination, TIME_FORMAT(ADDTIME(journey.departure, SEC_TO_TIME(SUM(pl2.time))), '%H:%i') AS departure, operator.code AS operator, bus.registration
  FROM journey
  INNER JOIN journey_day ON journey_day.journey = journey.id
  INNER JOIN service ON service.id = journey.service
  INNER JOIN operator ON operator.id = service.operator
  INNER JOIN line ON line.service = service.id
  INNER JOIN pattern ON pattern.id = journey.pattern
  INNER JOIN pattern_link pl ON pl.section = pattern.section AND pl.from_stop = :stop
  INNER JOIN pattern_link pl2 ON pl2.section = pattern.section AND pl2.sequence &lt; pl.sequence
  LEFT JOIN findmybus.bus ON bus.journey_id = journey.id
  WHERE journey_day.day = :day AND CURDATE() BETWEEN service.date_start and service.date_end AND operator.id = :operator
  GROUP BY journey.id, journey.departure
  HAVING (TIME_TO_SEC(journey.departure) + SUM(pl2.time)) &lt; 86400
  </code></pre>
  
  <p>Here is the result of an <code>EXPLAIN</code> statement prepended to the query:</p>
  
  <p><img src="https://i.stack.imgur.com/ln2YU.png" alt="enter image description here"></p>
  
  <p>Why is this query so slow? I have added indexes to all the columns that are joined in the <code>ON</code> clause. It seems like they are not all being used, why might this be?</p>
  
  <p>Results of query: <img src="https://i.stack.imgur.com/W2P8W.png" alt="enter image description here"></p>