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!


Similar Posts:

0 thoughts on “Always index your tables. Always”

  1. “…Safari could not open the page “http://iread.ro/” because the server is not responding.”
    Iar asta e asa cam de foo’… saptamana?
    Hai, inteleg ca nu mai ai entuziasm/te-ai plictisit… da’ macar anunta.

  2. Alex … ai un talent de a te imprastia in toate directiile (si proiectele) ceva de speriat! Mai ca ma ajungi din urma!
    D-aia si dormi prost noaptea.
    Sugestia mea (sincera, fara kkaturi idioate) :
    iread e ceva bun, la care chiar ai muncit, si pe care te pregatesti sa-l ingropi in bezna… degeaba!
    Fa-l sa MEARGA cum trebuie, pune dracu’ ceva google adsense, si mai te uiti o data pe luna la el. Nu cred ca cineva o sa se bosumfle ca pui reclame pe un sait la care tu platesti hosting si banda.
    Cum am mai zis si in alta parte, ma enervezi! Nu fiindca te-ai delasat (asta o inteleg) dar fiindca nu TERMINI ce-ai inceput! Rusine, alea.
    Uite si opinia mea (pentru cat valoreaza … ceva gen 2 lei vechi)
    Din toate agregatoarele pe care le-am vazut, al tau era cel mai bun…
    Hai, Alex, chiar vrei sa ma milogesc?
    Pune mana, o ora pe zi… intr-o saptamana, n-ai ce alege. 🙂

Leave a Reply