MySQL Forums
Forum List  »  Performance

Re: Slow Query, Optimization Advice Needed
Posted by: Rick James
Date: May 01, 2016 11:47PM

That cryptic correlated subquery is saying that you are only interested in the 'last' Location for each Trailer? That could be done with a "groupwise max" query:

SELECT  c1.nTrailerID, c1.nLocationID, c1.tsMovement
    FROM  tblMovements AS c1
    JOIN
      ( SELECT  nTrailerID, MAX(tsMovement) AS tsMovement
            FROM  tblMovements 
            GROUP BY  nTrailerID
      ) AS c2 USING (nTrailerID, tsMovement);
(Please run this to verify my claim.)

Note: tblMovements needs INDEX(nTrailerID, tsMovement)
Note: I am assuming the Trailer cannot be at two Locations at the same ts.

Now, putting it back together:

    select  count(0) AS nCount, L.nLocationID, L.nCapacity, TV.nSiteID
        FROM  
          ( SELECT  c1.nTrailerID, c1.nLocationID, c1.tsMovement
                FROM  tblMovements AS c1
                JOIN
                  ( SELECT  nTrailerID, MAX(tsMovement) AS tsMovement
                        FROM  tblMovements 
                        GROUP BY  nTrailerID
                  ) AS c2 USING (nTrailerID, tsMovement)
          ) AS M
        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

More discussion of "groupwise max": http://mysql.rjweb.org/doc.php/groupwise_max

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow Query, Optimization Advice Needed
396
May 01, 2016 11:47PM


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.