mysql query optimization

Question

Whet are things to look for when trying to optimize a mysql query that joins 7 tables?

select trips.tripid as tripid
     , stops.stopdescrption as "perron"
     , DATE_FORMAT(segments.segmentstart, "%H:%i") as "time"
     , DATE_FORMAT( trips.tripend, "%H:%i") as "arrival"
     , UPPER(routes.routepublicidentifier) as "lijn"
     , plcend.placedescrption as "destination" 
from calendar
                join trips on calendar.vsid=trips.vsid
                join routes on routes.routeid=trips.routeid
                join places plcstart on plcstart.placeid=trips.placeidstart
                join places plcend on plcend.placeid=trips.placeidend
                join segments on segments.tripid = trips.tripid
                join stops on segments.stopid = stops.stopid 
where ( stops.stopid = :perrons0
     OR stops.stopid = :perrons1  OR stops.stopid = :perrons2 
     OR stops.stopid = :perrons3  OR stops.stopid = :perrons4 
     OR stops.stopid = :perrons5  OR stops.stopid = :perrons6 
     OR stops.stopid = :perrons7  OR stops.stopid = :perrons8 
     OR stops.stopid = :perrons9  OR stops.stopid = :perrons10 
     OR stops.stopid = :perrons11 OR stops.stopid = :perrons12 
     OR stops.stopid = :perrons13 OR stops.stopid = :perrons14 
      ) 
  AND calendar.vscdate = DATE(DATE_ADD(now(), INTERVAL "07:00" HOUR_MINUTE)) 
  AND segments.segmentstart >= TIME(DATE_ADD(now(), INTERVAL "07:00" HOUR_MINUTE))   
  AND routes.routeservicetype = 0 
  AND segments.segmentstart > "00:00:00" 
ORDER BY segments.segmentstart

There's the query, I can't seem to think of anything to change that'll optimize this... it timeouts....

any tips are welcome!

Do you have any indexes?

Lost in Alabama
– Lost in Alabama

2011-08-29 18:39:15 +00:00
Commented Aug 29, 2011 at 18:39 — Lost in Alabama
– Lost in Alabama, Commented Aug 29, 2011 at 18:39
What does the EXPLAIN plan show?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2011-08-29 18:47:29 +00:00
Commented Aug 29, 2011 at 18:47 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 29, 2011 at 18:47

reggie · Accepted Answer · 2011-08-29 18:56:48Z

1

What about using the IN keyword instead of multiple OR. Also, you dont need to specify AND segments.segmentstart > "00:00:00" since you have already provided a condition segments.segmentstart >= TIME(DATE_ADD(now(), INTERVAL "07:00" HOUR_MINUTE)) which is greater than "00:00:00". Lastly, indexing your keys will be a good idea to optimize execution.

select trips.tripid as tripid
 , stops.stopdescrption as "perron"
 , DATE_FORMAT(segments.segmentstart, "%H:%i") as "time"
 , DATE_FORMAT( trips.tripend, "%H:%i") as "arrival"
 , UPPER(routes.routepublicidentifier) as "lijn"
 , plcend.placedescrption as "destination" 
from calendar
            join trips on calendar.vsid=trips.vsid
            join routes on routes.routeid=trips.routeid
            join places plcstart on plcstart.placeid=trips.placeidstart
            join places plcend on plcend.placeid=trips.placeidend
            join segments on segments.tripid = trips.tripid
            join stops on segments.stopid = stops.stopid 
where  stops.stopid IN (:perrons0,
 :perrons1,:perrons2, 
 :perrons3, :perrons4, 
 :perrons5, :perrons6, 
 :perrons7,:perrons8, 
 :perrons9, :perrons10, 
 :perrons11, :perrons12, 
 :perrons13, :perrons14 
  ) 
AND calendar.vscdate = DATE(DATE_ADD(now(), INTERVAL "07:00" HOUR_MINUTE)) 
AND segments.segmentstart >= TIME(DATE_ADD(now(), INTERVAL "07:00" HOUR_MINUTE))   
AND routes.routeservicetype = 0 

ORDER BY segments.segmentstart

edited Aug 29, 2011 at 18:56

answered Aug 29, 2011 at 18:50

reggie

13.8k13 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mars Over a year ago

WOW this did the trick, but I had tried this before and it just kept loading... must have been a server error!

Collectives™ on Stack Overflow

mysql query optimization

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related