How to know storage engine used of a database?

Question

Previously, on every database created, I use:

mysql -u root -p
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL ON dbname.* TO 'dbuser'@'localhost';

and then use the database without thinking about MyISAM or InnoDB

score 16 · Answer 1 · answered Mar 08 '11 at 16:54

16

You can check per-table like this:

USE <database>;
SHOW TABLE STATUS\G

you'll get an output along these lines:

root@localhost/database> show table status\G
*************************** 1. row ***************************
           Name: tablename
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 101
 Avg_row_length: 70
    Data_length: 7070
Max_data_length: 19703248369745919
   Index_length: 2048
      Data_free: 0
 Auto_increment: 1004
    Create_time: 2009-12-07 20:15:53
    Update_time: 2010-11-10 21:55:01
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment:

answered Mar 08 '11 at 16:54

Coops

5,967
1
31
52

any way you know of to do all tables in a DB (besides writing a script to `SHOW TABLE` for each table in the DB)? – voretaq7 Mar 08 '11 at 17:15
2

@voretaq7 the "SHOW TABLE STATUS" does show you all tables by default. – Coops Mar 08 '11 at 17:21
cool -- I'm a postgres guy so all this MySQL and multiple storage engines stuff is strange to me :-) – voretaq7 Mar 08 '11 at 17:38

score 6 · Answer 2 · answered Mar 08 '11 at 17:29

6

This query lists all InnoDB tables and their databases in MySQL:

SELECT table_name, table_schema 
FROM information_schema.tables 
WHERE engine = 'InnoDB';

You can also list all tables and their storage engines:

SELECT table_name, table_schema, engine
FROM information_schema.tables;

answered Mar 08 '11 at 17:29

Jari Jokinen

181
1

I tried the second query - I get the error: `ERROR 1146 (42S02): Table 'information_scheme.tables' doesn't exist` (on MySQL 5.6.37) – nnyby May 03 '18 at 16:59

score 5 · Answer 3 · answered Mar 08 '11 at 17:00

5

Use the 'show engine' command to view the active default engine

Add default-storage-engine=InnoDB in [mysqld] section of the my.cnf file for the default engine to be active.

Use the 'show create table table_name' command to view default engine in the table.

answered Mar 08 '11 at 17:00

Waldemar Junior

51
1

`SHOW ENGINE` gives me ERROR 1064 (42000). – mstrap Jul 05 '17 at 08:26
1

```show engines;``` – w00t Nov 08 '17 at 16:19

score 1 · Answer 4 · answered Aug 16 '16 at 04:34

1

To get engine name for a specific table

use <database_name>
show table status like '<table_name>';

To change engine

alter table <table_name> engine <engine_name>;

answered Aug 16 '16 at 04:34

user370681

11
1

How to know storage engine used of a database?

4 Answers4