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?

Answer 1

SHOW TABLE STATUS WHERE Name = 'xxx'

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

Answer 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.

Answer 3

SHOW CREATE TABLE <tablename>;

Less parseable but more readable than SHOW TABLE STATUS.

Answer 4

or just

show table status;

just that this will llist all tables on your database.

Answer 5

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.

Answer 6

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.

Answer 7

mysqlshow -i <database_name>

will show the info for all tables of a specific database.

mysqlshow -i <database_name> <table_name> 

will do so just for a specific table.

Answer 8

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.

Answer 9

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.

Answer 10

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_%';

Answer 11

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 < <({ 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 

.
.
.

Answer 12

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;

Answer 13

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.