HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Any gotchas at all with converting from MyISAM to InnoDB?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
allwithinnodbanygotchasmyisamconvertingfrom

Problem

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.

Solution

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

  • 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

  • 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

Code Snippets

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);

Context

StackExchange Database Administrators Q#10407, answer score: 7

Revisions (0)

No revisions yet.