Re: optimize request with order by and limit

[Rick James]

> So how can i rearrange the fields in the tables ?

By redesigning the tables so that the fields you need in the WHERE clause are all in one table. (This may not be possible and/or not practical.)

Let's dissect this:
L1 - PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 10
L2 - PRIMARY st eq_ref PRIMARY,StatusTypeID PRIMARY 3 foo.StatusTypeId 1
L3 - DERIVED trkenv range PRIMARY PRIMARY 4 (NULL) 151048 Using where; Using index; Using temporary; Using filesort
L4 - DERIVED trk eq_ref PRIMARY,JobID,TrkTypeID,TrkID_TrkTypeID_JobID PRIMARY 4 ixpath.trkenv.TrkID 1 Using where
L5 - DERIVED jb eq_ref PRIMARY PRIMARY 4 ixpath.trk.JobID 1

S1 The code will start with line L3 -- which is a 'range' scan in the 'index' of trkenv. It may need to touch about 151048 rows.
S2 For each of the rows in Step S1, L4 says to reach into trk, but very efficiently ('eq_ref' and '1') to find and check whether you still need to keep it.
S3 Now move on to jb, again with an 'eq_ref'.
S4 Sort the rows that survived S1..S3 to achieve the ORDER BY.
S5 Keep only the first 10 rows (LIMIT 0, 10). Note that up to now, you may have been hauling around thousands of rows. We are now finished creating <derived2>
S6 Foreach row in <derived2> (only 10 rows; see L1), ...
S7 Reach into st (L2) via foo.StatusTypeId. This will toss rows when there is no matching row, so you could end up with fewer than 10 rows.