Find which tables use INNODB with MySQL

Posted June 10th, 2009 in MySql

I recently needed to find which tables across all MySQL databases on a particular server used INNODB as the storage engine. This short post shows the query required to do this.

The SQL to do this queries the INFORMATION_SCHEMA and is as follows:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';

table_schema is the name of the database.

table_name is (obviously) the name of the table.

This may take some time to run especially if there are a lot of databases and/or tables. Some example data returned from the above query looks like this where there are two databases (test1 and test2) with a couple of tables each that use the INNODB storage engine:

+-------------------+----------------------------------------+
| table_schema      | table_name                             |
+-------------------+----------------------------------------+
| test1             | sessions                               |
| test1             | users                                  |
| test2             | products                               |
| test2             | categories                             |
+-------------------+----------------------------------------+
4 rows in set (2.08 sec)

In my next MySQL post I'll look at how the INNODB storage engine by default puts all the tables into a single file called ibdata1 and how you can change the default to have a single file per table in the same way that the MyISAM storage engine does.