FreeBSD6 Unleashed

Optimizing Database Performance

No matter how well you have designed your database schema, as your site becomes more and more popular and heavily used, it is likely that certain parts of its functionality will bog down. Perhaps you’ve forgotten an index in a certain crucial table. Perhaps you’re doing a complex join where a series of temporary tables would be computationally cheaper. Or perhaps your dynamic pages are simply far too complex, and you need to create a cache of static information that’s updated on a regular basis in order to speed up on-demand queries at the expense of instantaneous accuracy.

At the top-level view, it might seem impossible to figure out what’s slowing things down; all you see might be a perpetually high load average ...