OPTIMIZE TABLE  copies the data part of the table and rebuilds the indexes. The benefits come from improved packing of data within indexes, and reduced fragmentation within the tablespaces and on disk. The benefits vary depending on the data in each table. You may find that there are significant gains for some and not for others, or that the gains decrease over time until you next optimize the table. This operation can be slow if the table is large or if the indexes being rebuilt do not fit into the buffer pool. The first run after adding a lot of data to a table is often much slower than later runs. 

•In InnoDB , having a long PRIMARY KEY  (either a single column with a lengthy value, or several columns that form a long composite value) wastes a lot of disk space. The primary key value for a row is duplicated in all the secondary index records that point to the same row. (See Section 15.6.2.1, “Clustered and Secondary Indexes” .) Create an AUTO_INCREMENT  column as the primary key if your primary key is long, or index a prefix of a long VARCHAR  column instead of the entire column. 

•Use the VARCHAR  data type instead of CHAR  to store variable-length strings or for columns with many NULL  values. A CHAR( N )  column always takes N  characters to store data, even if the string is shorter or its value is NULL . Smaller tables fit better in the buffer pool and reduce disk I/O. 

When using COMPACT  row format (the default InnoDB  format) and variable-length character sets, such as utf8  or sjis , CHAR( N )  columns occupy a variable amount of space, but still at least N  bytes. 

•For tables that are big, or contain lots of repetitive text or numeric data, consider using COMPRESSED row format. Less disk I/O is required to bring data into the buffer pool, or to perform full table scans. Before making a permanent decision, measure the amount of compression you can achieve by using COMPRESSED  versus COMPACT  row format. 

## 8.5.2 Optimizing InnoDB Transaction Management 

To optimize InnoDB  transaction processing, find the ideal balance between the performance overhead of transactional features and the workload of your server. For example, an application might encounter performance issues if it commits thousands of times per second, and different performance issues if it commits only every 2-3 hours. 

•The default MySQL setting AUTOCOMMIT=1  can impose performance limitations on a busy database server. Where practical, wrap several related data change operations into a single transaction, by issuing SET AUTOCOMMIT=0  or a START TRANSACTION  statement, followed by a COMMIT  statement after making all the changes. 

InnoDB  must flush the log to disk at each transaction commit if that transaction made modifications to the database. When each change is followed by a commit (as with the default autocommit setting), the I/ O throughput of the storage device puts a cap on the number of potential operations per second. 

•Alternatively, for transactions that consist only of a single SELECT  statement, turning on AUTOCOMMIT helps InnoDB  to recognize read-only transactions and optimize them. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”  for requirements. 

•Avoid performing rollbacks after inserting, updating, or deleting huge numbers of rows. If a big transaction is slowing down server performance, rolling it back can make the problem worse, potentially taking several times as long to perform as the original data change operations. Killing the database process does not help, because the rollback starts again on server startup. 

To minimize the chance of this issue occurring: 

•Increase the size of the buffer pool so that all the data change changes can be cached rather than immediately written to disk. 

