MySQL Forums :: Performance :: Slow Query, Optimization Advice Needed


Advanced Search

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
Slow Query, Optimization Advice Needed 720 Michael Lawson 04/26/2016 09:30AM
Re: Slow Query, Optimization Advice Needed 349 Øystein Grøvlen 04/27/2016 01:02AM
Re: Slow Query, Optimization Advice Needed 406 Michael Lawson 04/27/2016 07:20PM
Re: Slow Query, Optimization Advice Needed 356 Peter Brawley 04/27/2016 11:28PM
Re: Slow Query, Optimization Advice Needed 336 Øystein Grøvlen 04/28/2016 01:25AM
Re: Slow Query, Optimization Advice Needed 338 Rick James 05/01/2016 11:47PM
Re: Slow Query, Optimization Advice Needed 348 Øystein Grøvlen 05/02/2016 01:28AM
Re: Slow Query, Optimization Advice Needed 344 Øystein Grøvlen 04/28/2016 01:12AM


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.