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

Question

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables. However, you can specify a storage engine to be used during one session with:

SET storage_engine=InnoDB;

So you don't have to specify it for each table.

How do I confirm, if indeed all the tables are using InnoDB?

possible duplicate of [How can I check MySQL engine type for a specific table?](http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table) — Alastair Irvine, Mar 12 '15 at 04:38

TehShrike · Accepted Answer · 2010-12-26T23:08:25.873

153

If you use SHOW CREATE TABLE, you have to parse the engine out of the query.

Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely).

The correct query to use is SHOW TABLE STATUS - you can get information on all the tables in a database:

SHOW TABLE STATUS FROM `database`;

Or for a specific table:

SHOW TABLE STATUS FROM `database` LIKE 'tablename';

One of the columns you will get back is Engine.

edited Dec 26 '10 at 23:08

answered Dec 23 '10 at 04:08

TehShrike

9,855
2
33
28

+1, except you always have permission to see the information schema for the objects you have permission to. – Konerak Dec 26 '10 at 22:59
@Konerak Ah, true enough - I'll correct the answer. As an aside, I have seen some _bugs_ where selecting from information_schema did not work in cases where people had the correct permissions. – TehShrike Dec 26 '10 at 23:09

The Scrum Meister · Answer 2 · 2011-06-15T00:54:52.723

18

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'db name' AND ENGINE != 'InnoDB'

edited Jun 15 '11 at 00:54

answered Dec 23 '10 at 02:48

The Scrum Meister

29,681
8
66
64

Only caveat is data in INFORMATION_SCHEMA is cached, so best to use [FLUSH TABLES](http://dev.mysql.com/doc/refman/5.0/en/flush.html) prior to the statement you provided – OMG Ponies Dec 23 '10 at 04:15
It helps me to count the no of InnoDB tables in DB. – Anon30 Jun 10 '15 at 10:47

score 5 · Answer 3 · answered Dec 23 '10 at 02:50

5

show create table <table> should do the trick.

answered Dec 23 '10 at 02:50

Madison Caldwell

862
1
7
16

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

3 Answers3

Linked