Re: optimize request with order by and limit

[yassine laouija]

explain
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


give :

- PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 10
- PRIMARY st eq_ref PRIMARY,StatusTypeID PRIMARY 3 foo.StatusTypeId 1
- DERIVED trkenv range PRIMARY PRIMARY 4 (NULL) 151048 Using where; Using index; Using temporary; Using filesort
- DERIVED trk eq_ref PRIMARY,JobID,TrkTypeID,TrkID_TrkTypeID_JobID PRIMARY 4 ixpath.trkenv.TrkID 1 Using where
- DERIVED jb eq_ref PRIMARY PRIMARY 4 ixpath.trk.JobID 1


thanks.