Find the default storage engine used in MySQL

Default storage engine used by MySQL can be found from information_schema database available in MySQL.

1) Login to mysql prompt

[root@dhcppc3 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.67-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sampledata         |
| test               |
+--------------------+
4 rows in set (0.02 sec)

2) Switch to information_schema database

mysql> use information_schema;

3) In the SUPPORT cloumn lookout for value DEFAULT. This tells which is the default storage engine.
As per below result, MyISAM is the default storage engine.


mysql> select * from ENGINES\G;
*************************** 1. row ***************************
      ENGINE: MRG_MYISAM
     SUPPORT: YES
     COMMENT: Collection of identical MyISAM tables
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
*************************** 2. row ***************************
      ENGINE: CSV
     SUPPORT: YES
     COMMENT: CSV storage engine
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
*************************** 3. row ***************************
      ENGINE: MyISAM
     SUPPORT: DEFAULT
     COMMENT: Default engine as of MySQL 3.23 with great performance
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
*************************** 4. row ***************************
      ENGINE: InnoDB
     SUPPORT: YES
     COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
          XA: YES
  SAVEPOINTS: YES
*************************** 5. row ***************************
      ENGINE: MEMORY
     SUPPORT: YES
     COMMENT: Hash based, stored in memory, useful for temporary tables
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
5 rows in set (0.00 sec)

How to find the storage engine set for a table?

Tables can be created with different storage engines. All tables in a database need not have the same storage engine. Also, the storage engine for a table too can be altered after creation.

To find the storage engine set for a table in database, it can done using TABLES table in database INFORMATION_SCHEMA.

mysql> select TABLE_NAME,TABLE_SCHEMA, ENGINE from tables where TABLE_NAME like '%dg_cities%'\G;
*************************** 1. row ***************************
  TABLE_NAME: dg_cities
TABLE_SCHEMA: sampledata
      ENGINE: MyISAM
1 row in set (0.00 sec)

  

So here the table dg_cities in database sampledata uses MyISAM storage engine.