@madpilot makes

The need for speed

If you are a DBA, and your reading this – look away now, because I’m pretty sure they covered this in Database Optimisation 101 and you WILL laugh at me having this revelation. 88 Miles hasn’t been the snappiest web application around lately thanks mainly to an influx of users (NOT that I’m complaining :P). I’d successfully added some views to speed up some of the reporting recently, and I went through today and optimised a lot of code, but it still wasn’t as quick as I would have liked it (A page load in the main index page was taking on average 1.5 seconds – down from the 4 seconds pre-optimisations).

I was googling the performance differences between INNER and LEFT joins (INNER wins most of the time for those of you playing at home), and came across a word that I vaguley remembered between dozing off in my Database class at university – indexes. Now, don’t get me wrong, I KNEW these things existed, I even knew what they did, but because I don’t use them regularly, I didn’t even think to look at them. As all of the primary IDswere already primary keys, my gaze turned to the foreign keys (I use the term relatively loosely – they were foreign keys in the sense that they referred to another table ID, not because they had been explicity setup that way).

I added indexes to the foreign keys on the three main tables, and voila! A ~10x speed increase on that front page! It’s such a simple optimisation too! *Slaps head*


  1. Jaja, it is difficult to belive that you did't index the foreing keys for a speed encrease :)
  2. You realise you'll never get work as a DBA ever now after this :-)
  3. @Boris: it's one of those things that unless you are doing it everyday, you just don't think about. I'll definately be thinking about it from now on!

    @NathanaelB What a shame... :)
  4. Add indexes to all the fields you're doing WHERE clause comparisons on too

Leave a comment