Am I right that InnoDb is better for frequent concurrent updates and inserts than MyISAM?

Question

We have a websites with hundreds of visitors every day and tens of thousands queries a day. So, some tables in the database are updated very rarely, some tables are updated few times a minute and some tables are updated ~10 times a seconds.
MyISAM uses table-level locking for updates and InnoDb uses row-level locking.
So, as I understand, for tables with frequent concurrent updates (several updates per second) it is better to make them InnoDb, and for other tables (if we don't need transactions and foreign keys of course) it is ok to be with MyISAM engine.
Am I right in my thoughts?

Todhunter

Answer 1

MyISAM is faster for reads and writes, but not at the same time. In other words, if you need to do a lot of writes, it will be faster, but if you want to also have people reading at the same time the readers and the writers will block each other and you may have concurrency issues. You should use InnoDB in such scenarios, and most mysql gurus recommend using InnoDB by default anyway because it's considered more generally reliable than MyISAM, despite being slower under some use cases.

Perdure

Yes, I forgot to mention that I mean CONCURRENT updates of course. Thank you for your answer. – Todhunter

Answer 2

Yes, I believe so. I am a newcomer to MySQL myself, but when I installed it, it explained that innoDB is optimised for transactional, operational systems, and MyISAM is great for reporting/DW systems.

Subscribe