MyISAM and InnoDB tables in one database
innodb, myisam, mysql
Solution
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.
Problem
I have a database with about 30 tables and 5 tables of them is write-intensive. I'm considering Convert 5 write-intensive tables to use InnoDB engine and keep the rest on MyISAM engine 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.