Re: Optimizer not working adequately

[EM Ayllon]

"As the name field is varchar, there's no way for MySQL to directly know the address of any row, so for LIMIT 30000,10 it actually has to scan the table from row 0 to row 30009 to find the requested rows. "

Why not? In the first instance MySQL found row 30000 to 300010 without needing any info from 'name', in fact as you say, without even reading data from table. MySQL should be able to locate those rows in the same fashion as in the first query and then obtain the 'name' info by reading data from those particular rows. Can not see MySQL being 'very clever' if it can not work out something as simple as this.

As for the increase in performance by adding an 'order' it actually does not seem to be a 'boost' but rather just a little help:

Without order: 0.58 secs
with order: 0.45 secs

Explain shows that it still considers all rows in the table:

explain select id,name from people order by id limit 30000,10 ;
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------+
| 1 | SIMPLE | people| index | NULL | PRIMARY | 4 | NULL | 42003 | |
+----+-------------+------------+-------+---------------+---------+---------+------+-------+-------+