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

User · Answer

go to information schema database there you will find  tables  table then select it   Mysql use information schema  Mysql  select table name engine from  tables

User · Answer

SHOW TABLE STATUS WHERE Name    xxx   This will give you  among other things  an Engine column  which is what you want

User · Answer

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

User · Answer

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

User · Answer

SHOW CREATE TABLE  lt tablename gt  G   will format it much nicer compared to the output of  SHOW CREATE TABLE  lt tablename gt     The  G trick is also useful to remember for many other queries commands

User · Answer

mysqlshow -i  lt database name gt    will show the info for all tables of a specific database   mysqlshow -i  lt database name gt   lt table name gt     will do so just for a specific table

User · Answer

SHOW CREATE TABLE  lt tablename gt  G   will format it much nicer compared to the output of  SHOW CREATE TABLE  lt tablename gt     The  G trick is also useful to remember for many other queries commands

User · Answer

SHOW TABLE STATUS WHERE Name    xxx   This will give you  among other things  an Engine column  which is what you want

User · Answer

SHOW CREATE TABLE  lt tablename gt     Less parseable but more readable than SHOW TABLE STATUS

User · Answer

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

User · Answer

If you re using MySQL Workbench  right-click a table and select alter table  In that window you can see your table Engine and also change it

User · Answer

SHOW CREATE TABLE  lt tablename gt     Less parseable but more readable than SHOW TABLE STATUS

User · Answer

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

User · Answer

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

User · Answer

go to information schema database there you will find  tables  table then select it   Mysql use information schema  Mysql  select table name engine from  tables

User · Answer

SHOW CREATE TABLE  lt tablename gt     Less parseable but more readable than SHOW TABLE STATUS

User · Answer

Yet another way  perhaps the shortest to get status of a single or matched set of tables   SHOW TABLE STATUS LIKE  table     You can then use LIKE operators for example   SHOW TABLE STATUS LIKE  field data

User · Answer

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

User · Answer

SHOW TABLE STATUS WHERE Name    xxx   This will give you  among other things  an Engine column  which is what you want

User · Answer

If you are a linux user   To show the engines for all tables for all databases on a mysql server  without tables information schema  mysql  performance schema   less  lt   lt    for i in   mysql -e  show databases     cat   grep -v -e Database-e information schema -e mysql -e performance schema   do echo  -------------------- i--------------------    mysql -e  use  i  show table status    done     column -t    You might love this  if you are on linux  at least   Will open all info for all tables in less  press -S to chop overly long lines   Example output    --------------------information schema-------------------- Name                                                        Engine              Version  Row format  Rows   Avg row length  Data length  Max data length     Index length  Data free  Auto increment  Create time  Update time  Check time  C CHARACTER SETS                                              MEMORY              10       Fixed       NULL   384             0            16434816            0             0          NULL            2015-07-13   15 48 45     NULL        N COLLATIONS                                                  MEMORY              10       Fixed       NULL   231             0            16704765            0             0          NULL            2015-07-13   15 48 45     NULL        N COLLATION CHARACTER SET APPLICABILITY                       MEMORY              10       Fixed       NULL   195             0            16357770            0             0          NULL            2015-07-13   15 48 45     NULL        N COLUMNS                                                     MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15 48 45     2015-07-13  1 COLUMN PRIVILEGES                                           MEMORY              10       Fixed       NULL   2565            0            16757145            0             0          NULL            2015-07-13   15 48 45     NULL        N ENGINES                                                     MEMORY              10       Fixed       NULL   490             0            16574250            0             0          NULL            2015-07-13   15 48 45     NULL        N EVENTS                                                      MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15 48 45     2015-07-13  1 FILES                                                       MEMORY              10       Fixed       NULL   2677            0            16758020            0             0          NULL            2015-07-13   15 48 45     NULL        N GLOBAL STATUS                                               MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15 48 45     NULL        N GLOBAL VARIABLES                                            MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15 48 45     NULL        N KEY COLUMN USAGE                                            MEMORY              10       Fixed       NULL   4637            0            16762755            0

User · Answer

or just   show table status    just that this will llist all tables on your database

User · Answer

If you re using MySQL Workbench  right-click a table and select alter table  In that window you can see your table Engine and also change it

User · Answer

SHOW TABLE STATUS WHERE Name    xxx   This will give you  among other things  an Engine column  which is what you want

User · Answer

mysqlshow -i  lt database name gt    will show the info for all tables of a specific database   mysqlshow -i  lt database name gt   lt table name gt     will do so just for a specific table

User · Answer

Yet another way  perhaps the shortest to get status of a single or matched set of tables   SHOW TABLE STATUS LIKE  table     You can then use LIKE operators for example   SHOW TABLE STATUS LIKE  field data

User · Answer

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

User · Answer

or just   show table status    just that this will llist all tables on your database

User · Answer

SHOW CREATE TABLE  lt tablename gt     Less parseable but more readable than SHOW TABLE STATUS

User · Answer

If you are a linux user   To show the engines for all tables for all databases on a mysql server  without tables information schema  mysql  performance schema   less  lt   lt    for i in   mysql -e  show databases     cat   grep -v -e Database-e information schema -e mysql -e performance schema   do echo  -------------------- i--------------------    mysql -e  use  i  show table status    done     column -t    You might love this  if you are on linux  at least   Will open all info for all tables in less  press -S to chop overly long lines   Example output    --------------------information schema-------------------- Name                                                        Engine              Version  Row format  Rows   Avg row length  Data length  Max data length     Index length  Data free  Auto increment  Create time  Update time  Check time  C CHARACTER SETS                                              MEMORY              10       Fixed       NULL   384             0            16434816            0             0          NULL            2015-07-13   15 48 45     NULL        N COLLATIONS                                                  MEMORY              10       Fixed       NULL   231             0            16704765            0             0          NULL            2015-07-13   15 48 45     NULL        N COLLATION CHARACTER SET APPLICABILITY                       MEMORY              10       Fixed       NULL   195             0            16357770            0             0          NULL            2015-07-13   15 48 45     NULL        N COLUMNS                                                     MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15 48 45     2015-07-13  1 COLUMN PRIVILEGES                                           MEMORY              10       Fixed       NULL   2565            0            16757145            0             0          NULL            2015-07-13   15 48 45     NULL        N ENGINES                                                     MEMORY              10       Fixed       NULL   490             0            16574250            0             0          NULL            2015-07-13   15 48 45     NULL        N EVENTS                                                      MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15 48 45     2015-07-13  1 FILES                                                       MEMORY              10       Fixed       NULL   2677            0            16758020            0             0          NULL            2015-07-13   15 48 45     NULL        N GLOBAL STATUS                                               MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15 48 45     NULL        N GLOBAL VARIABLES                                            MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15 48 45     NULL        N KEY COLUMN USAGE                                            MEMORY              10       Fixed       NULL   4637            0            16762755            0

User · Answer

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

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

Examples related to mysql

Examples related to innodb

Examples related to myisam