Using MySQL index to query ORDER BY LIMIT

Question

I use the "users" table with over 2 million entries. Inquiry:

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

"firstname" is indexed. Getting the first pages is very fast, while the last pages are very slow.

I used EXPLAIN and here are the results:

for

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

I get:

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 get

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 is the problem?

Answer 1

You should not use a limit on a page that is far from your data set.

You will get the best results. using range queries.

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

last_used_name - , ( , ). , . , .

LIMIT 100000 , 30

MySQL ,

LIMIT 100030

100 . .

Answer 2

* WHERE MySQL .

:

SELECT *
FROM (
  SELECT user_id
  FROM users
  WHERE 1
  ORDER BY firstname
  LIMIT $start, 30) ids
JOIN users
USING (user_id);

user_id .