Any gotchas at all with converting from MyISAM to InnoDB?

Q:

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table will throw a warning, except the manual page for ALTER TABLE. It's that kind of thing I need to know about before converting over. I thought I'd be fine with my queries but apparently not.

A:

Here are some gotchas

Memory Usage

MyISAM

• only caches index pages.
• shared keycache (sized by key_buffer_size).
• You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

• caches data pages and index pages.
• one buffer pool and one size before MySQL 5.5
• 1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

FULLTEXT Indexes

MyISAM

• Supports FULLTEXT indexes

InnoDB

• Before MySQL 5.6, no
• Starting with MySQL 5.6, yes, but still in beta

This means you cannot convert MyISAM to InnoDB. To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from(select table_schema,table_name
    from information_schema.tables
    where engine='MyISAM'and table_schema NOTIN('information_schema','mysql')) tbl
INNERJOIN(select table_schema,table_name
    from information_schema.statisticswhere index_type='FULLTEXT') ndx
USING(table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until MySQL 5.6 goes GPL

OPTIMIZE TABLE

MyISAM

• The MyISAM table is shrunk
• ANALYZE TABLE runs index statistics on all indexes

InnoDB

• ANALYZE TABLE is totally useless because index stats are always being recollected
• With innodb_file_per_table disabled, ibdata1 will grow bigger
• With innodb_file_per_table enabled, tablespace (.ibd file) is shrunk

 

参考：

http://dba.stackexchange.com/questions/10407/any-gotchas-at-all-with-converting-from-myisam-to-innodb