This is well known case when EXPLAIN lies.
It will actually only look at one row and send it.

If you're soring by unindexed column and have "filesort" in explain all rows will really be scanned.

Hi Peter,

Thank you very much for your answer.

I had this impression as the explain said using index in the extra but I wasn't sure.

But, unfortunately, we have a caseof the same req template, where the req takes about 4s (card=670+K lines) as follows:

explain extended SELECT username, user_id FROM phpbb_users ORDER BY user_regdate ASC LIMIT 661600, 50;+----+-------------+-------------+-------+---------------+--------------+---------+------+--------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------------+-------+---------------+--------------+---------+------+--------+-------+| 1 | SIMPLE | phpbb_users | index | NULL | user_regdate | 4 | NULL | 672173 | | +----+-------------+-------------+-------+---------------+--------------+---------+------+--------+-------+1 row in set, 1 warning (0.00 sec)


If we invert the ORDER BY from ASC to DESC and replace the LIMIT boundaries, we've got 1e-3 secs:

SELECT username, user_id FROM phpbb_users ORDER BY user_regdate DESC LIMIT 1, 50;


Which gives:

EXPLAIN EXTENDED SELECT username, user_id FROM phpbb_users ORDER BY user_regdate DESC LIMIT 1, 50;+----+-------------+-------------+-------+---------------+--------------+---------+------+--------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------------+-------+---------------+--------------+---------+------+--------+-------+| 1 | SIMPLE | phpbb_users | index | NULL | user_regdate | 4 | NULL | 672184 | | +----+-------------+-------------+-------+---------------+--------------+---------+------+--------+-------+1 row in set, 1 warning (0.00 sec)


Hence the questions:
- Why does it take so much time when using LIMIT bignumber,tinynumber? To me, it sounds MySql does not actually take advantage of the index on user_regdate. (It certainly read index pages from #1 to #bignumber. When we reverse the boundaries, it will only read from 1 to tinynumber, hence the dramatic speed up.
- Explain should have said rows= 661600+50 inthe first req and 50 in the second, right ?

What do you think ?

Thanks again for your interest in this question.

Large limits can get really slow.

Executing LIMIT 10000, 1

MySQL has to traverse 10000 rows (by index) and throw them away and when get one row and return to you. Not to mention it will do data read for each row even than index only lookup could be possible.

MySQL does use index though.

And explain... yes it could be fixed.

Currently EXPLAIN does not account for LIMIT any how.

It could but it does not )

Optimizer however knows about LIMIT and provides different plan with and without limit.