Performance with MySQL and paginating large tables

If you’re using the will_paginate Rails plugin to manage the amount of data that you show to the user on a single page, there is a gotcha that comes into play as your data gets bigger.

The default display of the pagination buttons for shows links that can take you to the last page of the set. If you only have a few thousand records, you’ll never notice the problem, but try it with a million records and check out the MySQL slow log file.

The SQL query generated by your paginate call will look something like:

1  SELECT * from foos ORDER BY created_at LIMIT 1000000,50

In the mysql-slow.log file you’ll eventually start seeing an entry that looks like this:

1  # Query_time: 6  Lock_time: 0  Rows_sent: 50  Rows_examined: 100050
2  SELECT * from foos ORDER BY created_at LIMIT 1000000,50

The key here is that MySQL had to look at 1M rows to return the 50 you requested!

This is a Very Bad Thing.

You must do all you can to avoid this case. What you do will depend on your specific application. The easiest is to not let page numbers go above a certain maximum, and configure the will_paginate view helper to not show all the possible page numbers.