Since MySQL version 4.1, InnoDB has provided two options for how tables are stored on disk. You can choose to create a new table and its indexes in the shared system tablespace (corresponding to the set of files named ibdata files), along with other internal InnoDB system information. Or, you can chose to use a separate file (an .ibd file) to store a new table and its indexes.

The tablespace style used for new tables is determined by the setting of the configuration parameter innodb_file_per_table at the time a table is created. Previously, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server. Beginning with the InnoDB storage engine, the configuration parameter innodb_file_per_table is dynamic, and can be set ON or OFF using the SET GLOBAL command. The default setting is OFF, so new tables and indexes are created in the system tablespace. Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.

Tables created when innodb_file_per_table is disabled cannot use the new compression capability, or use the new row format DYNAMIC. Tables created when innodb_file_per_table is enabled can use those new features, and each table and its indexes will be stored in a new .ibd file.

The ability to change the setting of innodb_file_per_table dynamically is useful for testing. As noted above, the parameter innodb_file_format is also dynamic, and must be set to “Barracuda” to create new compressed tables, or tables that use the new row format DYNAMIC. Since both parameters are dynamic, it is easy to experiment with these table formats and the downgrade procedure described in Chapter 11, Downgrading the InnoDB Storage Engine without a system shutdown and restart.

Note that the InnoDB storage engine can add and drop a table’s secondary indexes without re-creating the table, but must recreate the table when you change the clustered (primary key) index (see Chapter 2, Fast Index Creation in the InnoDB Storage Engine). When a table is recreated as a result of creating or dropping an index, the table and its indexes will be stored in the shared system tablespace or in its own .ibd file just as if it were created using a CREATE TABLE command (and depending on the setting of innodb_file_per_table). When an index is created without rebuilding the table, the index is stored in the same file as the clustered index, regardless of the setting of innodb_file_per_table.