Database Performance Tuning 101

Database performance tuning tends to be overlooked until that critical moment when a large group of simultaneous users hit your site and it grinds to a crawl. At that point, people start looking for the magic “fast” setting that with a simple adjustment will fix the timeouts and deathly slow response times. Rarely is life that simple. Building a robust and scalable web site requires starting with a well designed application and database schema. Failure to start with a good design is like building a house on a bad foundation, you put too much weight on it and everything will come crashing down.

One of the quickest and easiest ways to improve performance is through the use of indexes. Imagine looking for a particular word in a book and searching through each page one-by-one. It’s going to take time to find what you’re looking for, even for a computer. Now multiply that time by 100 users and you can quickly see why things will start breaking. What if you created an index in the back of the book that listed each phrase and what page it was listed on? Now your search is going to take 1/500^th of what it did before.

Database indexes are by no means a fix-all solution; implemented improperly they can do more harm than good. A quick Google search should get you enough info to get started and avoid the pitfalls.

Stay tuned for more tips next week…

[photo under CC by flickr user richardmasoner]