mysql 查询优化

Question

尝试优化连接 7 个表的 mysql 查询时需要注意什么？

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

有一个查询，我似乎想不出任何可以优化这个的改变......它超时......

欢迎任何提示!

Answer 1

使用 IN 关键字而不是多个 OR 怎么样。此外，您不需要指定 AND snippets.segmentstart > "00:00:00" 因为您已经提供了条件 segments.segmentstart >= TIME(DATE_ADD(now(), INTERVAL) "07:00"HOUR_MINUTE)) 大于 "00:00:00"。最后，为键建立索引将是优化执行的好主意。

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