## 8.5.6 Optimizing InnoDB Queries 

To tune queries for InnoDB  tables, create an appropriate set of indexes on each table. See Section 8.3.1, “How MySQL Uses Indexes”  for details. Follow these guidelines for InnoDB  indexes: 

•Because each InnoDB  table has a primary key (whether you request one or not), specify a set of primary key columns for each table, columns that are used in the most important and time-critical queries. 

•Do not specify too many or too long columns in the primary key, because these column values are duplicated in each secondary index. When an index contains unnecessary data, the I/O to read this data and memory to cache it reduce the performance and scalability of the server. 

•Do not create a separate secondary index for each column, because each query can only make use of one index. Indexes on rarely tested columns or columns with only a few different values might not be helpful for any queries. If you have many queries for the same table, testing different combinations of columns, try to create a small number of concatenated indexes rather than a large number of single- column indexes. If an index contains all the columns needed for the result set (known as a covering index), the query might be able to avoid reading the table data at all. 

•If an indexed column cannot contain any NULL  values, declare it as NOT NULL  when you create the table. The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL  values. 

•You can optimize single-query transactions for InnoDB  tables, using the technique in Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” . 

## 8.5.7 Optimizing InnoDB DDL Operations 

•Many DDL operations on tables and indexes ( CREATE , ALTER , and DROP  statements) can be performed online. See Section 15.12, “InnoDB and Online DDL”  for details. 

•Online DDL support for adding secondary indexes means that you can generally speed up the process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded. 

•Use TRUNCATE TABLE  to empty a table, not DELETE FROM tbl_name . Foreign key constraints can make a TRUNCATE  statement work like a regular DELETE  statement, in which case a sequence of commands like DROP TABLE  and CREATE TABLE  might be fastest. 

•Because the primary key is integral to the storage layout of each InnoDB  table, and changing the definition of the primary key involves reorganizing the whole table, always set up the primary key as part of the CREATE TABLE  statement, and plan ahead so that you do not need to ALTER  or DROP  the primary key afterward. 

## 8.5.8 Optimizing InnoDB Disk I/O 

If you follow best practices for database design and tuning techniques for SQL operations, but your database is still slow due to heavy disk I/O activity, consider these disk I/O optimizations. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. 

•Increase buffer pool size 

When table data is cached in the InnoDB  buffer pool, it can be accessed repeatedly by queries without requiring any disk I/O. Specify the size of the buffer pool with the innodb_buffer_pool_size option. This memory area is important enough that it is typically recommended that 

