MyISAM and InnoDB tables in one database

Question

I have a database with about 30 tables and 5 tables of them is write-intensive.

I'm considering

1. Convert 5 write-intensive tables to use InnoDB engine and keep the rest on MyISAM engine

2. Convert all tables to use InnoDB engine.

I wonder which approach is better?

To be more specific

The reason I want to keep some table on MyISAM engine is some of them has around 1,000,000 rows. I'm not sure how slower it will be for queries like "SELECT COUNT(*)" on these tables after converted to InnoDB.

I haven't done a test. I prefer getting some advices from any of you before start the switch.

Buran

Why not have the best of the two worlds: MySQL PBXT? – Siloa

Answer 1

These days, I always default to using InnoDB, especially on the write-intensive tables you mention where MyISAM suffers from full table locking. Here's a to-the-point comparison.

Reasons to use MyISAM:

• Tables are really fast for select-heavy loads
• Table level locks limit their scalability for write intensive multi-user environments.
• Smallest disk space consumption
• Fulltext index
• Merged and compressed tables.

Reasons to use InnoDB:

• ACID transactions
• Row level locking
• Consistent reads – allows you to reach excellent read write concurrency.
• Primary key clustering – gives excellent performance in some cases.
• Foreign key support.
• Both index and data pages can be cached.
• Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still - recover to the consistent state- No check / repair like MyISAM may require.
• All updates have to pass through transactional engine in InnoDB, which often decreases - performance compared to non-transactional storage engines.

The above was taken from this site, which no longer seems to be working.

Katherinakatherine

Your follow up concern regarding COUNT(*) is well-founded. InnoDB does not keep an internal count of rows in a table, so performing a SELECT COUNT(*) does do a full table scan. Whereas such a query would require no time with MyISAM, an equivalent InnoDB query could take many seconds for 1mil rows. The typical solution is to maintain another "counter table" where you would manually increment and decrement depending on whether you inserted or removed a countable row. You can find more here: dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html under "SHOW TABLE STATUS". – Katherinakatherine

Answer 2

pros and cons for each.

for (1) pros: less disk space usage, myisam much faster for read-heavy access patterns

cons: memory must be shared between the innodb buffers and myisam key buffers. innodb tables are about 4x bigger than their myisam counterparts. programmatic code must be adapted for deadlock handling.

just remember innodb will also lock if you're changing an indexed column or primary key.

Binkley