MySQL Forums
Forum List  »  Performance

Re: Slow Query, Optimization Advice Needed
Posted by: Øystein Grøvlen
Date: May 02, 2016 01:28AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow Query, Optimization Advice Needed
848
May 02, 2016 01:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.