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.
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'
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.
How to change the storage engine
- PHP script to convert MySQL tables to a new storage engine (Monday, June 27th 2011)
- Find which tables use INNODB with MySQL (Wednesday, June 10th 2009)
- How to change the storage engine a MySQL table uses (Saturday, January 17th 2009)
- Renaming a table in MySQL (Wednesday, July 2nd 2008)
- Check if a MySQL table exists (Friday, June 13th 2008)
- Listing tables and their structure with the MySQL Command Line Client (Sunday, May 18th 2008)