Once InnoDB support has been verified, you can start using the engine in your table definitions. A simple test to interact with InnoDB can be run as follows, where we will create a table with the engine specified explicitly along with a primary key, which InnoDB uses to index data.

If you leave off the engine definition at the end of the table create statement then MySQL will create the table with the system default engine, which is defined by the startup variables: default-storage-engine or, alternately, storage_engine, both accomplish the same purpose.

If you plan to use InnoDB exclusively for your table engine definitions, it generally makes sense to ensure that tables are created, explicitly and implicitly, by changing one or both of those startup variables to InnoDB. As of MySQL Version 5.5, the default has been changed to InnoDB so depending on your version you may not need to explicitly define the variable.

mysql> create database test; use test;
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> CREATE TABLE `test` (
    ->   `id` int(8) NOT NULL auto_increment,
    ->   `data` varchar(255),
    ->   `date` datetime,
    ->  PRIMARY KEY  (`id`),
    ->  INDEX `date_ix` (`date`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

In the preceding table create statement, we created one table with three columns. These columns are described as follows:

Besides edge cases, these columns can be considered the bare minimum columns for relational data. The automatically incremented id field will allow InnoDB to efficiently index our data or rapid lookups, and the date field will allow us to easily sort and search our data based on the time it was created in the table.

These two columns are specifically important for InnoDB based tables, as other table engines can operate relatively quick and efficient full table scans without a primary key column if running a typical query such as SELECT COUNT(*) FROM test;. However, InnoDB requires a PRIMARY KEY column or other INDEX to read in order for the query results to remain expedient; thus our id column provides that performance gain. Instead of writing the inefficient latter query, we can now write SELECT COUNT(id) FROM test; and InnoDB will access the id column PRIMARY KEY index which results in a very fast result versus a full table scan. Similarly, we can sort our table data via the date column and get quickly-returned table data.

If you'd like to experiment with InnoDB features more easily or see how different versions of MySQL work with InnoDB, you can install a sandboxing tool such as MySQL Sandbox.