Managing & Using MySQL, 2nd Edition

Host Application Tuning

Good application design and programming practices are crucial to getting good performance from your MySQL application. No amount of query tuning can make up for inefficient code. We cover many of the details of database application design in Chapter 8, but you can follow these general guidelines for designing your applications to optimize performance:

Normalize your database

Elimination of redundancy from your database is critical for performance.

Denormalize your database where appropriate

On the other hand, sometimes performance demands require that you denormalize your database. A classic example of this is a nightly report that summarizes basic information. These types of reports often require sifting through large quantities of data to produce summaries. In this situation, you can create a “redundant” table that is updated with the latest summary information on a periodic basis. This summary table can then be used as a basis for your report.

Let the MySQL server do what it does well

This point may seem obvious, but it is frequently overlooked. For example, if you need to retrieve a set of rows from a table, you could write a loop in your host application to retrieve the rows:

for (int i = 0; i++; i< keymax) {
 select * from foobar where key=i;
 process the row
}

The problem with this approach is that MySQL has the overhead of parsing, optimizing, and executing the same query for every iteration of the loop. If you let MySQL retrieve all the rows at once, ...