To create an InnoDB table, use the CREATE TABLE statement without any special clauses. Formerly, you needed the ENGINE=InnoDB clause, but not anymore now that InnoDB is the default storage engine. (You might still use that clause if you plan to use mysqldump or replication to replay the CREATE TABLE statement on a server running MySQL 5.1 or earlier, where the default storage engine is MyISAM.)

-- Default storage engine = InnoDB.
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a));
-- Backwards-compatible with older MySQL.
CREATE TABLE t2 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

Depending on the file-per-table setting, InnoDB creates each table and associated primary key index either in the system tablespace, or in a separate tablespace (represented by a .ibd file) for each table. MySQL creates t1.frm and t2.frm files in the test directory under the MySQL database directory. Internally, InnoDB adds an entry for the table to its own data dictionary. The entry includes the database name. For example, if test is the database in which the t1 table is created, the entry is for 'test/t1'. This means you can create a table of the same name t1 in some other database, and the table names do not collide inside InnoDB.

To see the properties of these tables, issue a SHOW TABLE STATUS statement:

SHOW TABLE STATUS FROM test LIKE 't%';

In the status output, you see the row format property of these first tables is Compact. Although that setting is fine for basic experimentation, to take advantage of the most powerful InnoDB performance features, you will quickly graduate to using other row formats such as Dynamic and Compressed. Using those values requires a little bit of setup first:

set global innodb_file_per_table=1;
set global innodb_file_format=barracuda;
CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) row_format=dynamic;
CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) row_format=compressed;

Always set up a primary key for each InnoDB table, specifying the column or columns that:

For example, in a table containing information about people, you would not create a primary key on (firstname, lastname) because more than one person can have the same name, some people have blank last names, and sometimes people change their names. With so many constraints, often there is not an obvious set of columns to use as a primary key, so you create a new column with a numeric ID to serve as all or part of the primary key. You can declare an auto-increment column so that ascending values are filled in automatically as rows are inserted:

-- The value of ID can act like a pointer between related items in different tables.
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));
-- The primary key can consist of more than one column. Any autoinc column must come first.
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

Although the table works correctly without you defining a primary key, the primary key is involved with many aspects of performance and is a crucial design aspect for any large or frequently used table. Make a habit of always specifying one in the CREATE TABLE statement. (If you create the table, load data, and then do ALTER TABLE to add a primary key later, that operation is much slower than defining the primary key when creating the table.)

| grep -E -v '^(mysql|information_schema)$' \
| while read database; do