Index the crap out of it!

There’s something satisfying about seeing a particularly complicated database query’s execution time go from 0.7 seconds to 0.02 seconds. I’m working on a tricky report for a client whose database has tens of millions of rows, and I was becoming frustrated with the sluggish queries. So I sat down and started looking at the query, testing its performance, until I saw a couple of fields that I could index. I ran a few commands to do so, and BAM, it sped up by a factor of almost 40.

A wise man (I think it was Brian) once told me that the solution to most database query performance problems is to “index the crap out of it”. So true.