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 stats\G
*************************** 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)
Tags: create table, information_schema, MySQL, storage engine
s/alias/alas/
I love SHOW CREATE TABLE … \G and hate DESC as it’s useless for finding out anything important, like (composite) indexes and engine type and other options.
Another way to catch for instance all non-innodb tables in a db:
USE dbname
SHOW TABLE STATUS WHERE engine != ‘InnoDB’;
Yep, SHOW TABLE STATUS will accept a WHERE clause…
This also works, and gives you all the tables in a given database (Mysql >= 5.0)
select table_name, engine from information_schema.tables where table_schema=’dbname’
Use “show table status;” to quickly see the storage engine of all tables in the db.
[...] How do I find the storage engine of a MySQL table MySQL Expert | MySQL Performance | MySQL Consultin… __________________ I don't believe in rebirth. Actually, I never did in my whole lives. [...]