Had an interesting talk with one of the Mysql consultants last week and he pointed out that if you’re using range selects with Mysql where you are selecting data, but using only indexed columns in the where clause it still has to read all of the data from the preceding rows to get to the rows that you’re interested in.
If you had a query like “select * from table1 where a=5 limit 5000,5010”. Even if column a is indexed, Mysql will still fetch all of the data for rows 1-4999 and discard them before returning the 10 rows that you’re interested in. If your limit range is fairly deep this can cause a fair amount of disk access that is completely unnecessary. An easy way to avoid this is to first grab the ids that you’re interested in and then select the actual data. You can do this with either two queries or a subselect.