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.

2 Answers 2


Here are some gotchas

Memory Usage



  • 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.



  • Supports FULLTEXT indexes


  • 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
    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.



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


  • Thanks for the query, small typo though: "engin" instead of "engine"
    – Andrew
    Commented Jan 10, 2012 at 14:20
  • @RolandoMySQLDBA: You can add that InnoDB does not have spatial indices. Commented Jan 30, 2012 at 22:05

I think the biggest gotcha would be around innodb being transactional. You'll want to know if the MySQL libraries being used by your applications auto_commit by default or not.

Python, for example, does not auto commit. This means if an application was inserting a row right before closing it's connection that insert would now be rolled back after you alter to innodb. The python script for example would need to be sure to call connection.commit();

Another point of difference could be around around multi row inserts or updates. Consider a single multi row inser

insert into tbl values (...row1...),  (...row2...),  (...rowN....);

Consider what happens if there is some kind of error such as a unique key collision on row3. With MyISAM the first two rows would have been written, under innodb all rows being written would be rolled back leaving nothing written in the even of such an error.

With innodb you will enter the world of deadlocks. These aren't inherently bad unless they're occuring with such frequency to prevent any work from being done. However your applications will need to be coded in a such a way they anticipate deadlocks and handle them appropriately (which most likely means just retry).

Consider memory/storage limitations. Innodb is a lot more resource intensive than MyISAM. If you have enough RAM to keep your buffer pools large enough to accommodate all your tables then you're golden.

Look for tables that have large primary keys. Innodb's clustered indexing means each secondary index holds another copy of the corresponding row's PK. If you have 2 secondary indexes that means each rows PK is stored 3 times (PK + each index). If the pk spans across several column and large datatypes (char(N) for example) you can see how the index requirements can quickly explode under innodb.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.