MySQL Index usage for ORDER BY LIMIT query

Question

I'm using "users" table with over 2 millions records. The query is:

SELECT * FROM users WHERE 1 ORDER BY firstname LIMIT $start,30

"firstname" column is indexed. Getting first pages is very fast while getting last pages is very slow.

I used EXPLAIN and here are the results:

for

EXPLAIN SELECT * FROM `users` WHERE 1 ORDER BY `firstname` LIMIT 10000 , 30

I'm getting:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  users   index   NULL    firstname   194     NULL    10030 

But for

EXPLAIN SELECT * FROM `users` WHERE 1 ORDER BY `firstname` LIMIT 100000 , 30

I'm getting

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  users   ALL     NULL    NULL    NULL    NULL    2292912     Using filesort

What's the issue?

Answer 1

You shouldn't use limit to page that far into your dataset.

You'll get much better results by using range queries.

SELECT * FROM users 
WHERE firstname >= last_used_name 
ORDER BY firstname 
LIMIT 30

Where last_used_name is one that you already seen (I'm assuming that you do batch processing of some sort). You will get more accurate results if you do range queries on a column with unique index. This way you won't get the same record twice.

When you do

LIMIT 100000 , 30

MySQL essentially does the same as in

LIMIT 100030

Only it doesn't return first 100 thousands. But it sorts and reads them.