Re: Slow Query, Optimization Advice Needed

[Rick James]

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