Select specific fields for performance

Before

In the rails-bestpractices.com, rails best practices are saved as Post model. The database schema of the Post model is

create_table "posts", :force => true do |t|
  t.string   "title"
  t.text     "body"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.integer  "user_id"
  t.text     "formatted_html"
  t.text     "description"
  ...
end

The posts index page displays 10 posts. At the beginning, I used

class PostsController < ApplicationController
  def index
    @posts = Post.paginate(:page => params[:page])
  end
end

to fetch posts from database. The query sql is as follows.

SELECT `posts`.* FROM `posts`  LIMIT 0, 10

It was slow when the posts had large body.

Refactor

Then I began to improve the performance. In the posts index page, the body, formatted_html and updated_at are no use, but the body and formatted_html occupy lots of memory, for example, the average size of body is 2k and formatted_html is also 2k, the posts index page display 10 posts per page, so the system wastes 40k memory each posts index request, and it also wastes time to fetch them from database. So I decided to remove theses fields from query.

In rails3 there is a method "select" to fetch specific fields, in rails2 you can also use select option in find method. Here I only give you the rails3 way.

class Post < ActiveRecrod::Base
  INDEX_COLUMNS = column_names - ['body', 'formatted_html', 'updated_at']
end

class PostsController < ApplicationController
  def index
    @posts = Post.select(Post::INDEX_COLUMNS).paginate(:page => params[:page])
  end
end

Here rails tells database fetch all the fields except body, formatted_html and updated_at, now the query sql is as follows

SELECT id, title, created_at, user_id, description FROM `posts` LIMIT 0, 10

As you see, there are no body, formatted_html and updated_at in query sql, it is faster than before and use less memory.

Update: You should not select specific fields if you use memory object caching system, such as memcache.