How do I know if a MySQL table is using myISAM or InnoDB Engine?

MySQLMySQLi Database

To know a MySQL table is using MyISAM or InnoDB engine, you can use the command show status table. The syntax is as follows −

SHOW TABLE STATUS from yourDatabaseName LIKE ‘yourTableName’.

The above syntax tells about the specific table engine. Now you can apply the above syntax to know whether the MySQL table engine is using MyISAM or InnoDB.

Here, I have database ‘business’ and table ‘student’. The query is as follows −

mysql> show table status from business like 'student';

The following displays the engine our table ‘student’ is using −

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 | InnoDB | 10      | Dynamic    | 2    | 8192           | 16384       | 0               | 32768        | 0         | NULL           | 2018-10-01 12:26:57 | NULL       | NULL       | utf8mb4_unicode_ci | NULL       |       |          |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.09 sec)

To know the engine type of all the tables, you can use the following syntax −

SHOW TABLE STATUS FROM yourDatabaseName;

Apply the above syntax in the following query −

mysql> show table status from business;

The following is the output that displays all the engines −

raja
Published on 21-Dec-2018 17:44:32
Advertisements