Reputation: 40321

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

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?

Upvotes: 487

Answers (14)

Greg

Reputation: 321864

SHOW TABLE STATUS WHERE Name = 'xxx'

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

Upvotes: 619

MArk Guadalupe

Reputation:

Or just run this:

show table status;

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

Upvotes: 56

mabreu0

Reputation: 90

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

Upvotes: 0

T30

Reputation: 12242

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.

Upvotes: 5

mytuny

Reputation: 875

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.

Upvotes: 0

Zahid

Reputation: 408

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.

Upvotes: 11

Evan Donovan

Reputation: 766

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.

Upvotes: 22

Javier

Reputation: 62681

SHOW CREATE TABLE <tablename>;

Less parseable but more readable than SHOW TABLE STATUS.

Upvotes: 59

Jocker

Reputation: 3021

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.

Upvotes: 302

sjas

Reputation: 19787

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 

.
.
.

Upvotes: 2

magic

Reputation: 81

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.

Upvotes: 8

David Thomas

Reputation: 4406

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

Upvotes: 3

harsha vardhan

Reputation: 11

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;

Upvotes: 0

Nicholas

Reputation: 2225

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.

Upvotes: 12

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

Answers (14)

Related Questions