How to tell which storage engine a MySQL table uses

Posted January 13th, 2009 in MySql (Updated June 27th, 2011)

MySQL supports multiple storage engines (e.g. MyISAM, INNODB, etc) each with its pros and cons, and each table in a MySQL database can use a different storage engine. This post looks at how to work out which table storage engine is used by a MySQL table, using either a SQL query or using the web browser tool phpMyAdmin. The next MySQL post shows how to change the storage engine for a MySQL table.

SQL Query

After digging around in the phpMyAdmin code I worked out they determine the MySQL table storage engine by querying the INFORMATION_SCHEMA database. This is a special database which decribes information relating to the various databases on the server.

The query the "products" table of the "test" database to see which storage engine it is using, you would run this SQL query:

SELECT ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products'

The TABLE_SCHEMA is the name of the database, and TABLE_NAME is the table name you wish to query. The SQL query above will return the storage engine, assuming the database and table specified exists, and you have sufficient permissions.

If you wanted to see the storage engine for all tables in your database, do this instead:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'

phpMyAdmin

Rather than write a SQL query, you can easily see the storage engine for all tables using the phpMyAdmin tool. Simply log in and select the database and you'll see something like this:

The column highlighted with the red box shows the storage engine used for each table. The last line with the bold storage engine shows the default storage engine for the database.