How can we get an accurate view of the MySQL storage engine, and let me show you two ways to do it right.
1) Right way one: SHOW table STATUS from database library name where name= ' table name '
2) Mysqlshow-u Database login account-P
Sometimes, we want to see what type of storage engine is the following MySQL table, needless to say, the direct idea is to use the Show create TABLE command to view the command that creates the table, so that the engine that defines the table is the real storage engine of the table. This method is not wrong in most cases, but sometimes it is fatal error, because sometimes it is clear to see the engine =myisam, how will select COUNT (*) from the Tbl_name query speed how can really slow it. This situation typically occurs when the storage using the created table is not installed successfully, causing the table to be used by the default storage engine of the database. It is therefore not entirely correct to look at the MySQL table's storage engine using the show create TABLE command. The correct way is to use the following I introduced the two ways, these two ways to see that there is no problem, please read the following article carefully
Right way one: SHOW table STATUS from database library name where name= ' table name ';
[email protected]:/myhome$ mysql-uroot-p ' MyPassword ' 02.Welcome to the MySQL monitor. Commands end with; or \g. 03.Your MySQL Connection ID is 221 04.Server version:5.1.41-3ubuntu12.7 (Ubuntu) 05. 06.Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement. .08.mysql> SHOW TABLE STATUS from mytest where name= ' test '; 09.+------------+--------+---------+------------+------+----------------+-------------+ (omit partial result) 10.| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | (omit partial result) 11.+------------+--------+---------+------------+------+----------------+-------------+ (omit partial result) 12.| Test | MyISAM | 10 | fixed | 0 | 0 | 0 | (omit partial result) 13.+------------+--------+---------+------------+------+----------------+-------------+ (omit partial result) 14.1 rowIn Set (0.02 sec) 15. 16.mysql>
5. Correct way Two: mysqlshow -u Database login account-P ' database login account password ' --status database name table name
[email protected]:/myhome$ mysqlshow-uroot-p ' mypassword '--status mytest test 2.database:mytest wildcard:t EST 3.+------------+--------+---------+------------+------+----------------+-------------+ (omit partial result) 4.| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | (Omit partial result) 5.+------------+--------+---------+------------+------+----------------+-------------+ (omit partial result) 6.| Test | MyISAM | 10 | Fixed | 0 | 0 | 0 | (Omit partial result) 7.+------------+--------+---------+------------+------+----------------+-------------+ (omit partial result)
Reprinted from: http://www.cnblogs.com/clouds008/archive/2013/06/10/3130680.html
How to view the engines used by MySQL database tables (reprint)