How to display the Engine of a MySQL table?

To know whether a MySQL table is using MyISAM or InnoDB engine then you can use below syntax.

The below syntax can be used for multiple tables −

show table status from yourDatabaseName;

Here is the syntax that can be used for a specific table i.e. to know the engine of a table −

show table status from yourDatabaseName Like ‘yourTableName’.

The following is the query to display engine of all the tables −

mysql> show table status from sampleTest;

The following is the output −

+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name          | Engine  | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment   |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| datetimedemo | InnoDB | 10      | Dynamic    | 0    | 0              | 16384       | 0               | 0             | 0         | NULL          | 2018-12-05 09:22:54 | NULL                | NULL  | utf8mb4_0900_ai_ci      | NULL      |               |         |
| primarydemo  | InnoDB | 10      | Dynamic    | 0    | 0              | 16384        | 0              | 0             | 0          | NULL         | 2018-12-05 09:23:34 | NULL                | NULL   | utf8mb4_0900_ai_ci | NULL |                         |         |
| student      | MyISAM | 10      | Dynamic    | 0    | 0         |    | 281474976710655 | 1024 | 0 | 1 | 2018-12-05 09:22:22 | 2018-12-05 09:22:23 | NULL | utf8mb4_0900_ai_ci | NULL | | |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
3 rows in set (0.19 sec)

The following is the query to display engine type for a specific table −

mysql> show table status from sampletest Like 'student';

The following is the output that displays the engine for only “student” table −

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| student | MyISAM | 10      | Dynamic    | 0    | 0              | 0 | 281474976710655 | 1024 | 0 | 1 | 2018-12-05 09:22:22 | 2018-12-05 09:22:23 | NULL | utf8mb4_0900_ai_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

Vrundesha Joshi

Updated on: 25-Jun-2020

951 Views

Kickstart Your Career

Get certified by completing the course

Get Started