13.4 Optimizing the Logical Database Structure

This section describes techniques for organizing data that can help you achieve better query performance.

13.4.1 Choosing Appropriate Table Types

When creating a table, ask yourself what types of queries you'll use it for. Then choose a table type that uses a locking level appropriate for the anticipated query mix. MyISAM table-level locking works best for a query mix that is heavily skewed toward retrievals and includes few updates. Use InnoDB if you must process a query mix containing many updates. InnoDB's use of row-level locking and multi-versioning provides good concurrency for a mix of retrievals and updates. One query can update rows while other queries read or update different rows of the table.

If you're using MyISAM tables, choose their structure to reflect whether you consider efficiency of processing speed or disk usage to be more important. Different MyISAM storage formats have different performance characteristics. This influences whether you choose fixed-length or variable-length columns to store string data:

• Use fixed-length columns (CHAR) for best speed. Fixed-length columns allow MySQL to create the table with fixed-length rows. The advantage is that fixed-length rows all are stored in the table at positions that are a multiple of the row length and can be looked up very quickly. The disadvantage is that fixed-length values are always the same length even for values that do not use the full width of the column, so the column takes more storage space.

• Use variable-length columns (VARCHAR, TEXT, BLOB) for best use of disk space. For example, values in a VARCHAR column take only as much space as necessary to store each value and on average use less storage than a CHAR column. The disadvantage is that variable-length columns result in variable-length rows. These are not stored at fixed positions within the table, so they cannot be retrieved as quickly as fixed-length rows. In addition, the contents of variable-length rows might not even be stored all in one place, another source of processing overhead.

For InnoDB tables, it is also true that CHAR columns take more space on average than VARCHAR. But there is no retrieval speed advantage for InnoDB as there is with MyISAM, because the InnoDB engine implements storage for both CHAR and VARCHAR in a similar way. In fact, retrieval of CHAR values might be slower because on average they require more information to be read from disk.

If a MyISAM table contains a mix of fixed-length and variable-length columns, the table format will be dynamic. However, if many of the queries on the table access only its fixed-length columns, it is sometimes possible to gain advantages both of static tables (faster retrieval) and of dynamic tables (lower storage requirements) by splitting the table into two tables. Use a fixed-format table to hold the fixed-length columns and a dynamic-format table to hold the variable-length columns. To split the table into two, use this procedure:

1. Make sure that the table contains a primary key that allows each record to be uniquely identified. (You might use an AUTO_INCREMENT column, for example.)

2. Create a second table that has columns for all the variable-length columns in the original table, plus a column to store values from the primary key of the original table. (This column should be a primary key as well, but should not be an AUTO_INCREMENT column.)

3. Copy the primary key column and the variable-length columns from the original table to the second table. The second table will be dynamic.

4. Use ALTER TABLE to drop the variable-length columns (but not the primary key) from the original table. MySQL will notice that the table no longer contains any variable-length columns and convert it to static format.

After modifying the table structure this way, queries that retrieve only fixed-width columns can use the static table, and will be quicker. For queries that retrieve both fixed-width and variable-width columns, join the two tables using the primary key values to match up rows.

Another option with MyISAM tables is to use compressed read-only tables.

For more information about MyISAM table structure, see section 14.2.1, "MyISAM Storage Formats."

MERGE tables can use a mix of compressed and uncompressed tables. This can be useful for time-based records. For example, if you log records each year to a different log file, you can use an uncompressed log table for the current year so that you can update it, but compress the tables for past years to save space. If you then create a MERGE table from the collection, you can easily run queries that search all tables together.