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?
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?
SHOW TABLE STATUS WHERE Name = 'xxx'
This will give you (among other things) an Engine column, which is what you want.
SHOW CREATE TABLE <tablename>;
Less parseable but more readable than SHOW TABLE STATUS.
show table status from database_name;
It will list all tables from the mentioned database.
Example output
show table status where name=your_desired_table_name;
It will show the storage engine used by the mentioned table.
If you are a GUI guy and just want to find it in PhpMyAdmin, than pick the table of your choice and head over the Operations tab >> Table options >> Storage Engine.
You can even change it from there using the drop-down options list.
PS: This guide is based on version 4.8 of PhpMyAdmin. Can't guarantee the same path for very older versions.
Apart from examples showed in previous entries, you can also get that from information_schema db with standard query as follows :
use information_schema;
select NAME from INNODB_TABLES where NAME like "db_name%";