GNU Info

(mysql.info)Using InnoDB tables

---

Next: Adding and removing Prev: InnoDB init Up: InnoDB

---

Enter node , (file) or (file)node

Creating InnoDB tables
----------------------

Suppose you have started the MySQL client with the command `mysql test'.
To create a table in the InnoDB format you must specify `TYPE = InnoDB'
in the table creation SQL command:

     CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

This SQL command will create a table and an index on column `A' into
the InnoDB tablespace consisting of the data files you specified in
`my.cnf'. In addition MySQL will create a file `CUSTOMER.frm' to the
MySQL database directory `test'.  Internally, InnoDB will add to its
own data dictionary an entry for table `'test/CUSTOMER''. Thus you can
create a table of the same name `CUSTOMER' in another database of
MySQL, and the table names will not collide inside InnoDB.

You can query the amount of free space in the InnoDB tablespace by
issuing the table status command of MySQL for any table you have
created with `TYPE = InnoDB'. Then the amount of free space in the
tablespace appears in the table comment section in the output of
`SHOW'. An example:

     SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Note that the statistics `SHOW' gives about InnoDB tables are only
approximate: they are used in SQL optimization. Table and index
reserved sizes in bytes are accurate, though.

NOTE: `DROP DATABASE' does not currently work for InnoDB tables!  You
must drop the tables individually. Also take care not to delete or add
`.frm' files to your InnoDB database manually: use `CREATE TABLE' and
`DROP TABLE' commands.  InnoDB has its own internal data dictionary,
and you will get problems if the MySQL `.frm' files are out of 'sync'
with the InnoDB internal data dictionary.

Converting MyISAM tables to InnoDB
..................................

InnoDB does not have a special optimization for separate index creation.
Therefore it does not pay to export and import the table and create
indexes afterwards.  The fastest way to alter a table to InnoDB is to
do the inserts directly to an InnoDB table, that is, use `ALTER TABLE
... TYPE=INNODB', or create an empty InnoDB table with identical
definitions and insert the rows with `INSERT INTO ... SELECT * FROM
...'.

To get better control over the insertion process, it may be good to
insert big tables in pieces:

     INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something
                                                  AND yourkey <= somethingelse;

After all data has been inserted you can rename the tables.

During the conversion of big tables you should set the InnoDB buffer
pool size big to reduce disk i/o. Not bigger than 80 % of the physical
memory, though.  You should set InnoDB log files big, and also the log
buffer large.

Make sure you do not run out of tablespace: InnoDB tables take a lot
more space than MyISAM tables. If an `ALTER TABLE' runs out of space,
it will start a rollback, and that can take hours if it is disk-bound.
In inserts InnoDB uses the insert buffer to merge secondary index
records to indexes in batches. That saves a lot of disk i/o. In
rollback no such mechanism is used, and the rollback can take 30 times
longer than the insertion.

In the case of a runaway rollback, if you do not have valuable data in
your database, it is better that you kill the database process and
delete all InnoDB data and log files and all InnoDB table `.frm' files,
and start your job again, rather than wait for millions of disk i/os to
complete.