Optimizer not working adequately

[emilio.ayllon]

I am investigating something as simple as this:

SELECT id FROM people LIMIT 30000,10;
where id is the primary key

which with EXPLAIN gives something like this:

+----+-------------+------------+-------+---------------+---------+---------+---
---+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | re
f | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+---
---+-------+-------------+
| 1 | SIMPLE | people | index | NULL | PRIMARY | 4 | NU
LL | 41988 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+---
---+-------+-------------+
1 row in set (0.00 sec)

Now, if I modify a little the expression:


SELECT id,name FROM people LIMIT 30000,10;
name being varchar(50)

+----+-------------+------------+------+---------------+------+---------+------+
-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+
-------+-------+
| 1 | SIMPLE | people | ALL | NULL | NULL | NULL | NULL |
41988 | |
+----+-------------+------------+------+---------------+------+---------+------+
-------+-------+

What I have observed is that with the second the query becomes much slower because it actually checks the total number of rows on the table according to the explanation above. I have also observed that the query becomes slower when the offset increases, i.e. limit 0, 10 is much faster than limit 30000,10 which is faster thatn limit 40000,10 and so on....

Can not see the reason why mysql does not optimize this type of query.

Regards,

Emilio.