How can I check MySQL engine type for a specific table?

Question

My MySQL database contains several tables using different storage engines (specifically myisam and innodb). How can I find out which tables are using which engine?

Solution 1

SHOW TABLE STATUS WHERE Name = 'xxx'

This will give you (among other things) an Engine column, which is what you want.

Solution 2

To show a list of all the tables in a database and their engines, use this SQL query:

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

Replace dbname with your database name.

Solution 3

SHOW CREATE TABLE <tablename>;

Less parseable but more readable than SHOW TABLE STATUS.

Solution 4

Bit of a tweak to Jocker's response (I would post as a comment, but I don't have enough karma yet):

SELECT TABLE_NAME, ENGINE
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = 'database' AND ENGINE IS NOT NULL;

This excludes MySQL views from the list, which don't have an engine.

Solution 5

SHOW CREATE TABLE <tablename>\G

will format it much nicer compared to the output of

SHOW CREATE TABLE <tablename>;

The \G trick is also useful to remember for many other queries/commands.

View more solutions