named scopes pwns will_paginate

will_paginate has its place. It's good for paginating when your queries are simple. My queries usually aren't simple, are yours? I noticed recently that one of my pages was taking a long time to display, around 1.9 seconds. I tracked it down to the will_paginate.

The problem is that will_paginate takes a single hash of ActiveRecord::Base#find arguments. That means if your query is really complex, then it's going to use that same complex query for both the result set and the count.

This is more or less what I was doing with will_paginate that it was choking on.

class Post < ActiveRecord::Base
  STATUS_OK = 1
  cattr_accessor :per_page
  per_page = 15
  has_many :watchers, ...
end

class PostsController < ApplicationController
  
  def index
    page_no = (params[:page] && params[:page].to_i) || 1
    @posts = Post.paginate :conditions => ["posts.status_id = ?", Post::STATUS_OK],
                           :order => 'group_id DESC, checksum', # don't ask, it's complicated
                           :include => :watchers,
                           :page => page_no
  end
  
end

The counting sql it makes out of that query does an outer join on watchers and selects distinct posts.id. Ouch. So the obvious solution is to simply do the count and result set queries ourselves. No biggie, but let's use named scopes to pretty things up.

class Post < ActiveRecord::Base
  STATUS_OK = 1
  cattr_accessor :per_page
  per_page = 15
  has_many :watchers, ...
  
  named_scope :ok, :conditions => {"posts.status_id" => Post::STATUS_OK}
  named_scope :recent, :order => "group_id DESC, checksum"
  named_scope :paginate, lambda { |page_no| {:offset => {(page_no-1)*per_page}, :limit => per_page} }
end

class PostsController < ApplicationController
  
  def index
    page_no = (params[:page] && params[:page].to_i) || 1
    @posts = Post.ok.recent.paginate(page_no).all(:include => :watchers)
    @post_count = Post.ok.count
  end
  
end

Pretty slick... and since we're doing the pagination "explicitly", we know that the counting is as simple as it needs to be. Let's take a look at the benchmarks, before and after.

# The SQL calls generated by will_paginate
Post Load (0.341523)
SELECT * FROM "posts" WHERE (post.status_id = 1) ORDER BY group_id DESC, checksum LIMIT 15 OFFSET 0
Watcher Load (0.005799)
SELECT "watchers".* FROM "watchers" WHERE ("watchers".page_id IN (...)) ORDER BY id
SQL (1.335042)
SELECT count(DISTINCT "posts".id) AS count_all FROM "posts" LEFT OUTER JOIN "watchers" ON watchers.post_id = posts.id WHERE (posts.status_id = 1)

# The SQL calls generated when we "manually paginate".
Post Load (0.350336)
SELECT * FROM "posts" WHERE ("posts"."status_id" = 1) ORDER BY group_id DESC, checksum LIMIT 15 OFFSET 0
Watcher Load (0.004582)
SELECT "watchers".* FROM "watchers" WHERE ("watchers".post_id IN (...)) ORDER BY id
SQL (0.023346)
SELECT count(*) AS count_all FROM "posts" WHERE ("posts"."status_id" = 1)

We save roughly 1.3 seconds by intelligently doing the counting. I really don't think using will_paginate buys you any cleaner code over using named scopes. Now all you have to do is implement a method similar to the will_paginate view helper and you can remove will_paginate from your project.