15.4 InnoDB-Specific Optimizations

Several strategies may be used with InnoDB to improve performance. Some of these can be used at the application level. Others are a result of the way that the database administrator configures InnoDB itself.

Application-level optimizations may be made in terms of how you design tables or issue queries:

• Use a primary key in each table, but make the key values as short as possible. InnoDB uses the primary key to locate the table rows. Other (secondary) indexes are keyed to the primary key values, which means that there is a level of indirection to find the table rows. Thus, shorter primary key values make for quicker lookups not only for queries that use the primary key, but also for queries that use secondary indexes. Secondary indexes will also take less space because each secondary index record contains a copy of the corresponding primary key value.

• Use VARCHAR columns rather than CHAR columns in InnoDB tables. The average amount of space used will be less, resulting in less disk I/O during query processing. (This behavior differs from that of MyISAM tables, which, due to their storage format, are faster for fixed-length columns than for variable-length columns.)

• Modifications made over the course of multiple statements should be grouped into a transaction whenever it makes sense to do so. This minimizes the number of flush operations that must be performed. For example, if you need to run 100 UPDATE statements that each modify a single row based on its primary key value, it's faster to run all the statements within a single transaction than to commit each one as soon as it executes. (A corollary to this principle is that you should avoid making updates with autocommit mode on. That causes the effects of each statement to be flushed individually.)

Administrative optimizations are possible through the way you configure InnoDB. The following list briefly mentions some of the possibilities:

• To reduce page flushing from the buffer pool, configure InnoDB to use larger log files.

• Choose a log flushing method that best matches your goals. You can opt to guarantee durability (no loss of committed changes), or to get faster performance at the possible cost of losing approximately the last second's worth of committed changes in the event of a crash.

• Use raw disk partitions in the tablespace to avoid a level of filesystem-access overhead normally incurred when using regular files.

For information on the options used to configure these aspects of InnoDB operation, see section 15.7, "InnoDB Maintenance."