MySQL Forums
Forum List  »  Optimizer & Parser

Re: optimize request with order by and limit
Posted by: Rick James
Date: July 20, 2013 10:25PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: optimize request with order by and limit
1446
July 20, 2013 10:25PM


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.