Always index your tables. Always

This is going to be brief one: iRead.ro, one of the projects I’m working on during my spare time, was giving me major headaches lately - 2 weeks ago, it took like 15 seconds to render a page. In the last couple of days, it was taking somewhere around 100 seconds to render it; obviously too much for the nginx server that was running my two mongrels.

I *had* indexed my tables, by id, url and several other fields. Turns out, they weren’t indexed with the right columns.

I went into the console and tried running the simple query used by the controller, in order to retrieve the FeedItems (where FeedItem < Item) between two boundary dates. Yep, the +100 seconds were there. Although the over 32000 items in the table were obviously not a *huge* number, perhaps their size(and the limits of my slicehost RAM) was the problem.

Quick fix:

./script/generate migration AddExtraIndex
class AddExtraIndex < ActiveRecord::Migration
  def self.up
    add_index :items, :type
    add_index :items, :date
  end

  def self.down
    #...
  end
end

Ran the migration and… here you go. iRead now renders the pages in around 3 seconds (without any caching whatsoever). Enjoy!