Gotchas When Converting from MyISAM to InnoDB

innodbmyisamMySQL

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.

Best Answer

Here are some gotchas

Memory Usage

MyISAM

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

  • Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
  • Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

MySQL 5.5 and back

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 NOT IN ('information_schema','mysql')
) tbl
INNER JOIN
(
    select table_schema,table_name
    from information_schema.statistics
    where index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

OPTIMIZE TABLE

MyISAM

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

InnoDB

Related Question