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?

score 598 · Accepted Answer · edited Jun 23 '22 at 15:24

598

SHOW TABLE STATUS WHERE Name = 'xxx'

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

edited Jun 23 '22 at 15:24

Giacomo1968

25,759
11
71
103

answered Oct 17 '08 at 19:30

Greg

316,276
54
369
333

5

If my table is damaged, it's simply shows Engine=NULL and has a "Comment" telling me to repair the table. However, before I know how to repair the table, I'm guessing I need to know the engine (I haven't gotten that far to be honest.) Therefore, @Jocker's comment below was much more helpful to me. – harperville Sep 09 '13 at 13:33
@Greg, Should we use this or Jocker's solution? – Pacerier Feb 24 '15 at 05:30
@Pacerier - Both work fine. The difference is that this solution gives the info for one table, while Jocker's solution shows the engine type for all tables in a database (while being a bit more readable as well). – kramer65 Mar 20 '16 at 08:36
@Greg, Btw why doesn't engine info show up when we do `show full columns from t1`? – Pacerier Nov 13 '17 at 02:13

score 294 · Answer 2 · edited Jul 28 '16 at 08:07

294

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.

edited Jul 28 '16 at 08:07

aalaap

4,145
5
52
59

answered Nov 19 '10 at 13:51

Jocker

2,941
1
14
2

5

This is great b/c it filters out everything but tablename and engine. – Tone Mar 23 '11 at 13:05
2

To get engine of specific table, you may use: `SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '' AND TABLE_NAME = ''` – userlond Apr 24 '17 at 03:10
1

This gives me an empty set on MySQL 5.6.37. – nnyby May 03 '18 at 16:57
4

This is a better answer because it's true SQL, so I can do things like `COUNT(*)` and `GROUP BY`. – Brandon Aug 08 '18 at 13:31

score 56 · Answer 3 · edited Mar 12 '17 at 13:08

56

SHOW CREATE TABLE <tablename>;

Less parseable but more readable than SHOW TABLE STATUS.

edited Mar 12 '17 at 13:08

Pacerier

86,231
106
366
634

answered Oct 17 '08 at 19:33

Javier

60,510
8
78
126

score 54 · Answer 4 · edited Jun 23 '22 at 15:24

54

Or just run this:

show table status;

This will list all tables and related details on your database.

edited Jun 23 '22 at 15:24

Giacomo1968

25,759
11
71
103

answered Aug 19 '09 at 04:29

2

@NikolaiSamteladze, Do have you tables in the selected db? – Pacerier Feb 24 '15 at 05:31
the best and easiest way! – Zia Yamin Nov 21 '21 at 07:07

score 21 · Answer 5 · edited Jan 06 '19 at 01:54

21

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.

edited Jan 06 '19 at 01:54

Alexis Wilke

19,179
10
84
156

answered Aug 12 '11 at 16:31

Evan Donovan

748
9
18

Zahid · Answer 6 · 2019-02-13T18:18:37.523

11

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.

edited Feb 13 '19 at 18:18

answered Feb 10 '19 at 19:36

Zahid

380
6
14

score 10 · Answer 7 · answered Jan 13 '13 at 18:18

10

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.

answered Jan 13 '13 at 18:18

Nicholas

2,147
3
23
31

1

\G hack is only under cli mysql – gaRex Jun 17 '15 at 19:38
Great solution thanks – Christian LSANGOLA Oct 10 '21 at 20:25

score 8 · Answer 8 · edited Jul 13 '15 at 13:44

8

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.

edited Jul 13 '15 at 13:44

sjas

18,644
14
87
92

answered Apr 11 '14 at 13:19

magic

81
1
1

T30 · Answer 9 · 2021-01-04T14:57:39.757

5

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.

edited Jan 04 '21 at 14:57

answered Sep 24 '15 at 07:58

T30

11,422
7
53
57

score 3 · Answer 10 · answered Jan 04 '15 at 07:12

3

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

answered Jan 04 '15 at 07:12

David Thomas

4,027
3
28
22

score 2 · Answer 11 · answered Jul 13 '15 at 13:52

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 

.
.
.

score 0 · Answer 12 · answered Aug 01 '13 at 08:28

0

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;

answered Aug 01 '13 at 08:28

harsha vardhan

11

score 0 · Answer 13 · answered Feb 14 '19 at 11:06

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.

score 0 · Answer 14 · edited Sep 23 '21 at 06:18

0

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%";

edited Sep 23 '21 at 06:18

Suraj Rao

29,388
11
94
103

answered Sep 23 '21 at 06:15

mabreu0

80
2
8

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

14 Answers14

Linked

Related