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:
SELECT * from foos ORDER BY created_at LIMIT 1000000,50In the mysql-slow.log file you’ll eventually start seeing an entry that looks like this:
# Query_time: 6 Lock_time: 0 Rows_sent: 50 Rows_examined: 100050
SELECT * from foos ORDER BY created_at LIMIT 1000000,50The 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.
Yes, WillPaginate and MySQL seem to be slow for large tables with a million records. I observed this, too. There is also a huge difference if you try to get all columns or if you only get the ids. My solution was to hack WillPaginate. I wrote about it here