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.

Answer 1

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.

Answer 2

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.