Possible Duplicate:
How can I check MySQL engine type for a specific table?
Assuming that users is a table following command does not reveal if users table is MyISAM or Innodb.
desc users;
How do I find what is the type of users table?
views:
45answers:
3
+1
A:
You can use SHOW CREATE TABLE and look for the ENGINE part in the response.
SHOW CREATE TABLE users;
Example:
CREATE TABLE innodb_table (id int, value int) ENGINE=INNODB;
CREATE TABLE myisam_table (id int, value int) ENGINE=MYISAM;
CREATE TABLE default_table (id int, value int);
Result for innodb_table:
SHOW CREATE TABLE innodb_table;
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| innodb_table | CREATE TABLE `innodb_table` (
`id` int(11) DEFAULT NULL,
`value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Result for myisam_table:
SHOW CREATE TABLE myisam_table;
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
| myisam_table | CREATE TABLE `myisam_table` (
`id` int(11) DEFAULT NULL,
`value` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Result for default_table:
SHOW CREATE TABLE default_table;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------+
| default_table | CREATE TABLE `default_table` (
`id` int(11) DEFAULT NULL,
`value` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Daniel Vassallo
2010-09-10 01:44:20
-1: This is a uselessly costly way of knowing the storage engine.
Andrew Moore
2010-09-10 01:51:35
@Andrew: What does it cost? ... Technically your answer is probably better, but the response of `SHOW CREATE TABLE` is not only easier to read (in my opinion) but also shorter to type.
Daniel Vassallo
2010-09-10 01:53:33
+1. This is probably the most common and easy to remember way to check the storage engine, even if slightly 'noisy.'
wuputah
2010-09-10 02:03:48
@Daniel: `SHOW CREATE TABLE` requires the database to fetch information about all indexes, columns and triggers currently defined on that table. This is not the case with `SHOW TABLE STATUS`.
Andrew Moore
2010-09-10 02:14:59
@Andrew - Who gives a toss about the cost of a one-time check on the storage engine?
Hammerite
2010-09-10 03:09:56
+2
A:
You can use SHOW TABLE STATUS to see table information.
SHOW TABLE STATUS WHERE `Name` = 'my_table';
Simply check the value of the Engine column in the returned dataset to know which engine the table is using.
Andrew Moore
2010-09-10 01:47:35
A:
SELECT ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='your_table_name'
AND TABLE_SCHEMA='your_database_name';
-- or use TABLE_SCHEMA=DATABASE() if you have a default one.
Wrikken
2010-09-10 02:37:52