How to tell which storage engine a MySQL table uses

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:

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: