在尝试优化连接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


有查询,我似乎想不出任何可以优化此功能的更改……它超时…。

欢迎任何提示!

最佳答案

如何使用IN关键字而不是多个OR。另外,您不需要指定AND segments.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

10-07 19:25
查看更多