MySQL: find out and change table storage engine

There is a simple way to find out current storage engine for any table(s) using SHOW TABLE STATUS query.

SHOW TABLE STATUS LIKE `User`;

Column Engine in result of query above shows current storage engine for table `User`.

SHOW TABLE STATUS LIKE `User%`;

Column Engine in result of query above (separate row for each table) shows current storage engine for tables like `User`, `UserPhoto`, `UserCommet` etc.

SHOW TABLE STATUS FROM `TestDB`;

Query above allows to find out storage engine for each table in database.

SHOW TABLE STATUS WHERE `Engine` = 'InnoDB';

And this query allows to get list of tables with storage engine InnoDB.

SQL query below helps to change table storage engine:

ALTER TABLE `User` ENGINE = MyISAM;

1 Comment so far

  1. abdussamad on March 18th, 2012

    Thanks. This was very useful. I used it to find out whether my db tables were all myisam so that I could use mysqlhotcopy instead of mysqldump to backup my databases.

Leave a reply