Home / server / Questions / 244755
In Process
cewebugil
Asked: 2011-03-09 08:40:07 +0800 CST

How to know storage engine used of a database?

  • 772

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

How to know storage engine used of a database?

mysql database innodb myisam

4 Answers

  1. 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:
    • 16

  2. 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;
    • 6

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

    • 5

  4. 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>;
    • 1