Pagination of long lists often works with SQL queries like the following one with an offset O and a limit number N:
SELECT * FROM table ORDER BY something LIMIT O, N
THe WILL PAGINATE plugin for example produces queries like this. They can be awful slow if the offset O is very large. A million rows have to be retrieved, stored, sorted, and then most of them are discarded because only a small number N is retrieved.
Somehow MySQL is much faster if you only select the ids first, and fetch the complete records later. For example if the offset is 40000, then a SELECT of the complete records may take 50 seconds and more, while fetching only the ids takes less than a second:
SELECT * FROM books ORDER BY year LIMIT 40000, 12; ==> 56 s SELECT id FROM books ORDER BY year LIMIT 40000, 12; ==> 0.3 s