@madpilot makes

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*