Last updated on November 21, 2020 by Dan Nanni
There are two major MySQL storage engines used:
Innodb. MyISAM is non-transactional, and thus can be faster for reads, while InnoDB fully supports transactions (e.g., commit/rollback) with finer-grain locking. When you create a new MySQL table, you choose its type (i.e., storage engine). Without any choice, you will simply use a pre-configured default engine.
If you would like to know the type of an existing MySQL database table, there are a couple of ways to do it.
phpMyAdmin, you can find out the database type from
phpMyAdmin. Simply choose your database from
phpMyAdminto see a list of its tables. Under
Typecolumn, you will see the database type for each table.
If you can log in to a MySQL server directly, another way to identify the storage engine is to run the following MySQL command inside your MySQL server after logging in.
mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';
The above command will show the engine type of a table called
Yet another method to check the MySQL engine is to use
mysqlshow, a command-line utility which shows database information. The
mysqlshow utility comes with MySQL client package installation. To use
mysqlshow, you need to supply MySQL server login credential.
The following command will show information about a particular database. Under
Engine column, you will see the storage engine for each table.
$ mysqlshow -u <mysql_user> -p -i <database-name>