Re: Slow Query, Optimization Advice Needed
Good idea, Rick! However, I think that an upgrade to 5.6 is necessary for your derived tables to work optimally. In 5.5, there will be no indexes on the derived table. Hence, one would have to process c2 before c1, and M before the rest of the tables for the join to be efficient. This means that one will need to go through movements of in-active trailers. So if the number of inactive trailers is significant, I think the total work in 5.5 will probably be higher than for the original query.
By the way, I do not think the outer derived table is necessary. I think this should also work:
select count(0) AS nCount, L.nLocationID, L.nCapacity, TV.nSiteID
FROM tblMovements M
JOIN( SELECT nTrailerID, MAX(tsMovement) AS tsMovement
FROM tblMovements
GROUP BY nTrailerID
) AS M2 USING(nTrailerID, tsMovement)
JOIN tblLocations L ON L.nLocationID = M.nLocationID
join tblTrailers T on M.nTrailerID = T.nTrailerID
join tblTrailerLogs TL on TL.nTrailerID = T.nTrailerID
join tblTrailerVendors TV on TV.nTrailerVendorID = T.nTrailerVendorID
where TL.nMovementOutID = 0
and T.eStatus = 'active'
group by L.nLocationID
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway