I have to admit, I was never formally trained as a database administrator. Other than an introductory database course that I took about 7 years ago, most of what I know about databases I have learned on my own or by talking to other people. As such, I am not the best database schema developer nor do I know much about SQL database optimization. As such, it shouldn't be that surprising that yesterday I found myself trying to optimize a page that was taking 4-5 seconds to process each record (for an estimated total of 45-minutes... if it didn't keep timing out).
Normally, I might look at this page and its fairly long and complex SQL algorithms and think to myself, 45 minutes is long, but maybe that's as good as its gonna get.
Well, Thank God I attended Nate Nelson's CFUNITED sessions on Database Performance Tuning and Beyond Basic SQL. Nate spent about a quarter of the time driving home just how important indexes are when it comes to database performance. He also repeatedly stressing the fact that databases are really amazing and that they should be super fast; that you should rarely expect a well built database to perform slowly. It was this message that I kept hearing in my head as I kept pouring over my huge ass SQL queries.
Instead of settling for less than even mediocre database performance, I took one of his [Nate's] optimization tips and I went through the various tables involved and I indexed all of the foreign keys. This is the first time I have ever indexed a foreign key. Heck, it's the first time that I've ever indexed anything but the primary key fields.
Re-running the page, I actually got giddy with excitement and drew many sideways glances with my uncontrollable school-girl giggling. The page, which had been taking an estimated 45 minutes to process had just finished processing in 16 seconds.
Let me make that as clear as possible: Taking Nate Nelson's SQL optimization advice and adding foreign key indexes to my tables, the page went from taking 45-minutes down to 16 seconds.
And so it was that on July 11th in the year of 2007, Ben Nadel saw the power of indexes and it was good.