How do I find the storage engine of a MySQL table

This seems quite a trivial question, but developers don’t often know what a MySQL storage engine is and how to determine what storage engine is used for a table.

The first choice is to describe the table with the DESC[RIBE] command. Side Note: people often don’t realize that DESC is a short acceptable version here.

mysql> desc stats;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| stat_id | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| created | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| version | tinyint(3) unsigned | NO   |     | NULL              |                |
| referer | varchar(500)        | NO   |     | NULL              |                |
| q       | varchar(100)        | YES  |     | NULL              |                |
| uri     | varchar(100)        | YES  |     | NULL              |                |
| server  | text                | NO   |     | NULL              |                |
| headers | text                | YES  |     | NULL              |                |
+---------+---------------------+------+-----+-------------------+----------------+

Alias, this command does not provide the details of the storage engine.
You need to use the SHOW CREATE TABLE as a means to get a more detailed description including the storage engine.

mysql> SHOW CREATE TABLE statsG
*************************** 1. row ***************************
       Table: stats
Create Table: CREATE TABLE `stats` (
  `stat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `version` tinyint(3) unsigned NOT NULL,
  `referer` varchar(500) NOT NULL,
  `q` varchar(100) DEFAULT NULL,
  `uri` varchar(100) DEFAULT NULL,
  `server` text NOT NULL,
  `headers` text,
  PRIMARY KEY (`stat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21964 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

As another side note tip, try the G instead of a semicolon (;) which provides a better vertical output.

Another option is to query the INFORMATION_SCHEMA.TABLES meta data.

mysql> SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=DATABASE();
+--------------+--------+
| table_name   | engine |
+--------------+--------+
| stats        | MyISAM |
+--------------+--------+
1 row in set (0.00 sec)