How to Check the Storage Engine of a Table in MySQL?

In our last post, we discussed how to check the Oracle client version on Windows/Linux. Today, let’s see how to check the storage engine of a table in MySQL.

How can you check the storage engine of a single table in MySQL and how can you check which tables in an entire database use a specific storage engine, such as MyISAM? Below is a brief summary of the key points related to this topic.

To check the storage engine of a single table, you can use the SHOW CREATE TABLE command to view the table's storage engine. Here are a few methods:

Method 1 to Check the Storage Engine of a Table in MySQL

mysql> show create table test;                 
+-------+----------------------------------------------+
| Table | Create Table                                 |
+-------+----------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------+
1 row in set (0.00 sec)
mysql>

Method 2 to Check the Storage Engine of a Table in MySQL

mysql> show table status from MyDB where name='test' \G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-06-09 15:45:00
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

Method 3 to Check the Storage Engine of a Table in MySQL:

mysql> 
mysql> select table_catalog
    ->       ,table_schema
    ->       ,table_name
    ->       ,engine
    -> from tables
    -> where table_schema='MyDB' and table_name='test';
+---------------+--------------+------------+--------+
| table_catalog | table_schema | table_name | engine |
+---------------+--------------+------------+--------+
| def           | MyDB         | test       | InnoDB |
+---------------+--------------+------------+--------+
1 row in set (0.00 sec)
 
mysql>

If you want to query the storage engines used by tables in a specific database or across the entire instance, you can use information_schema.tables. Below are a few simple examples.

Query tables using the MyISAM storage engine in the entire MySQL instance:

 select table_catalog
      ,table_schema
      ,table_name
      ,engine
from information_schema.tables
where engine='MyISAM';

Query tables using the MyISAM storage engine in the MyDB database:

select table_catalog
      ,table_schema
      ,table_name
      ,engine
from information_schema.tables
where engine='MyISAM';