Database optimization thoughts

If you’re working on heavy duty websites, knowing your database and how to use it best can make a world of difference in terms of performance, and thus you should always optimize the database. That’s pretty much obvious.

The tricky part is how you do the optimization? Often it requires a lot of reading up on how the database works - strengths, weaknesses and other details — and loads of experience. Having a DBA available to help you optimize would be ideal in some cases, but often you need to do it yourself.

So, is there a “free lunch” recipe with guidelines to help you do the correct optimization? Well, no. All database optimizations are usually case-specific, and the optimizations which worked last time may not be applicable in the current case.

There are however some generic rules, which may help you go in the right direction.

• Database optimization should start early. Think about performance when designing you database scheme - table layout and column types.
• Consider the transaction types during the data life cycles. Are you primarily doing reads or writes? How many columns is expected?
• Learn to use indexes - wisely. Too many indexes is just as bad as no indexes.
• Try to benchmark various table and column layouts and see how they perform - sometimes you might be surprised and other times just confirm theories.