I always find it interesting when I take someone else’s web development project, examine the queries being used, add a few good indices, and improve a script from 30 to 40 minutes to 5 to 10 seconds.

That has happened more than once, by the way.

I don’t think people realize how quick databases can be, if optimized correctly.

Crunching live data quickly and offering it to your clients can be a real asset on your site. I think many people shy away from offering live looks at data mainly because of the immense overhead that running reports can cause. They can tie up your processor (or processors) for minutes — sometimes even up to an hour or two.

Rare is the case where this is necessary, however.

The first step I would take is to study literature on how to optimize queries on your choice of database. Usually, this involves a combination of creating proper indices and rewriting queries to get the data in a less intensive way, sometimes by splitting a single difficult query up into two smaller queries.

The second step I would take is to make sure that you are storing your data in the best possible way. Sometimes redundant data is bad, because you have to worry about whether every place it is stored has been update properly. (Some databases make this easier than others.) Sometimes, however, redundancy helps you to join less tables together. And less joins is always better for speed. Weigh the trade offs, and make the best choice.

Although it is beyond the scope of this blog, other options that can be taken to improve performance dramatically include considering other database software options or increasing the number of servers you are utilizing (e.g. have a separate server only performing database queries).

Let’s improve our database queries. Database-driven web applications are usually much slower than they have to be, and it is only because of ignorance that they remain that way.

• Choosing an Available Script by David Hickenbotham
• Search Engine Friendly Table Structure - By Paul Madden
• SEO? What’s that?

This entry was posted on Thursday, September 29th, 2005 at 8:53 pm and is filed under Development Tips. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.