Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Previously, on every database created, I use:

mysql -u root -p
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?

share|improve this question

You can check per-table like this:

USE <database>;

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
share|improve this answer
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 8 '11 at 17:15
@voretaq7 the "SHOW TABLE STATUS" does show you all tables by default. – Coops Mar 8 '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 8 '11 at 17:38

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.

share|improve this answer

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;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.