Performance for SELECT using LIMIT and OFFSET

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

development

limit, MySQL, offset, Pagination, performance, select, sql

2008/11/06

Leave a comment