Re: optimize request with order by and limit

[Rick James]

You have

SELECT  SQL_NO_CACHE trk.TrkTypeID , trk.TrkID ,
        st.Alias
    FROM  tracking trk
    INNER JOIN  TrackingEnv trkenv ON trk.TrkID = trkenv.TrkID
    INNER JOIN  Job jb on jb.JobID=trk.JobID
    INNER JOIN  statustype st on st.StatusTypeID = jb.StatusTypeID
    WHERE  trk.TrkTypeID IN ( 2,3,10)
      AND  trkenv.EnvID IN ( 1,1,20 )
    Order BY  jb.StartDate DESC
    LIMIT  0 , 10

The source of the inefficiency is using 2 tables in the WHERE clause, plus a third in the ORDER BY, then paginating via OFFSET and LIMIT. It had to gather about 151146 rows from the first table (trk; picked as the best by the optimizer), then 1 row from each of the other tables. After all that, then it could do the ORDER BY and OFFSET and LIMIT.

Maybe you can improve the RAM usage:
http://mysql.rjweb.org/doc.php/memory

> 151146 | Using where;Using temporary;Using filesort

It _may_ help to delay accessing statustype until _after_ filtering the number of rows down to 10:

SELECT  TrkTypeID, TrkID, st.Alias
    FROM  
      ( SELECT  trk.TrkTypeID , trk.TrkID , jb.StartDate,
                jb.StatusTypeId
            FROM  tracking trk
            INNER JOIN  TrackingEnv trkenv ON trk.TrkID = trkenv.TrkID
            INNER JOIN  Job jb on jb.JobID=trk.JobID
            WHERE  trk.TrkTypeID IN ( 2,3,10)
              AND  trkenv.EnvID IN ( 1,1,20 )
            Order BY  jb.StartDate DESC
            LIMIT  0 , 10 ) foo
    JOIN  statustype st on st.StatusTypeID = foo.StatusTypeID;

> Using index

Another partial help would to be to create a covering index: In tracking, replace
KEY `TrkTypeID` (`TrkTypeID`),
with
INDEX (TrkTypeID, TrkID, JobID)

Shouldn't this be the PRIMARY KEY (that is, UNIQUE) in trackingenv?:
KEY `TrkID_EnvID` (`TrkID`,`EnvID`)



Edited 1 time(s). Last edit at 07/21/2013 12:08AM by Rick James.