You can determine table type with
SHOW CREATE TABLE table_name
or
SHOW TABLE STATUS LIKE 'table_name';
From the manual <http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html>, "If a storage engine is specified that is not available, MySQL uses MyISAM instead." That applies to ALTER as well as to CREATE. Starting with 4.1.1, you get a warning when that happens. In earlier versions, it's silent.
Also from the manual <http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html>, "ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then the original table is deleted and the new one is renamed." Your performance increase is probably due to better organized data and rebuilt indexes resulting from the copy operation.
Michael
James Green wrote:
Hi
Recently we performed an 'alter table' on a dev box to change from myisam to innodb, and it resulted in considerable speed improvement. in the lab setting.
Unfortunately when we repeated on the live server, whilst we got a 200% performance boost (estimate) again, we failed to notice that innodb is in DISABLED state, and yet alter table returned ok.
Looking at the tables on the disk, the only innodb references we can find were last modified a few days ago, whereas the myisam versions have the current date.
We therefore conclude that the alter table command failed siliently (really bad bug that), however we cannot explain the performance boost.
Clearly before shutting anything down or fixing things, we would like to know if these tables are safely still myisam. We would also like to know where the performance boost has come from - one of the alter tables commands took roughly 45 minutes to finish - it did something, we just don't know what!
Any help duly appreciated.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
