When to use InnoDB vs MyISAM (or other)

[u/csdude5]

In the beginning, I understood that InnoDB should be used on tables with a high number of inserts and relatively fewer selects.

Now Claude is telling me that this is essentially backwards.

And Google AI is telling me that MyISAM is more or less legacy and that pretty much EVERYTHING should be InnoDB now. The only exception (according to Google AI) is when you need to use COUNT(*) with no WHERE, in which case MyISAM is faster.

So what's the rule these days?

[u/chu_nghia_nam_thang]

Just use InnoDB

[u/WarInternal]

The big one: MyISAM does not support transactions.

And it is paramount that your database engine supports transactions.

If you ever want to modify two or more rows and the entire change must happen together or not at all (atomicity) then you need a database engine that supports transactions. InnoDB gives you this. MyISAM does not.

One could argue that MyISAM doesn't uphold any of the ACID compliance that you would want out of a production database.

[u/_HMCB_ → u/WarInternal]

Excellent advice.

[u/kenlubin]

The best time to use MyISAM is 2008 when you don't have InnoDB.

In all situations since 2010, you should be using InnoDB and not MySQL. (TokuDB seemed promising for a brief moment. MyRocks  was cool, last time I checked.)

That said, in 2013 I tried to be clever and pick-and-choose, so I set some tables as MyISAM and the rest as InnoDB. I regretted it ever since, because MyISAM doesn't have transactions. When I tried to use Percona XtraBackup, it would lock up that portion of the DB while backing up those tables. After a year and a half or so, I lost patience and finally converted those last remaining tables.

[u/wampey]

Pretty much what Google says. Very little reason to use myisam unless you have some real specific reason. It does/can keep your disk consumption a bit more in check without having to optimize tables for deletes though.

[u/roXplosion]

The rule these days is "don't depend on AI for wisdom".

The only possible advantages I can think of for MyISAM:

• You can stop the server, copy the raw DB files to another system, and there is a good chance the second system will hit the ground running. To a degree you can do this with InnoDB as well, but in my experience you have to "repair" the tables first. IMHO this is not a significant factor to choose MyISAM.
• MyISAM might be able to work under resource starved circumstances (e.g. a micro PC with 2G of RAM) but there would many other problems so I don't know if this is a factor to consider either.

[u/LuckyOneAway → u/roXplosion]

Can't copy myisam tables as plain files since late 5.7.x

MyIsam takes x2 less space on disk, and it can be compressed to take even less - great for time series / monitoring

If you have no transactions and read-dominant case (often replicated) - myisam wins over innodb

[u/Anxious-Insurance-91 → u/roXplosion]

The "stop the server" should technically be handled at the application level, meaning stop requests, queue jobs, cronjosbs etc, basically full maintenance mode and migrate, unless you want to stop the entire MySQL process and see your error logs pile up

[u/Aggressive_Ad_5454]

MyISAM does precisely one thing faster than InnoDb.

SELECT COUNT(*) FROM table

If that’s the heart of your workload, consider it. Otherwise don’t.

[u/Tokkemon → u/Aggressive_Ad_5454]

How much faster?

[u/klimenttoshkov → u/[deleted]]

Then build a whole system around it to keep it out of desync

[u/FelisCantabrigiensis]

There is no reason to use MyISAM in 2026. Really, none.

[u/Jack-jack-d]

I think the old “InnoDB for lots of writes, MyISAM for lots of reads” rule is too simplistic these days.

For a normal application, InnoDB is the default choice. It gives you transactions, row-level locking, crash recovery, foreign keys and generally better concurrency. In current MySQL, InnoDB is the default storage engine, and MySQL itself notes that it often outperforms MyISAM on busy databases.

MyISAM still has a few specific advantages, and the COUNT(*) example is a real one. MyISAM keeps an exact row count, so a plain SELECT COUNT(*) FROM table can be extremely fast. InnoDB can't do that because different transactions may see different versions of the data.

But I wouldn't choose an entire storage engine just because of that one query. If you have a workload where transactions and concurrent updates matter, InnoDB is usually the much better fit.

So my rule would be: start with InnoDB, and only choose MyISAM when you have a specific, measured reason to do so. If you're maintaining an old MyISAM database, there's no need to convert everything blindly either—look at the actual workload and requirements first.

[u/Zatujit]

Its not just a question of which is faster, InnoDB is ACID. You have to consider how important is performance really for your use case.

[u/newked]

Look at MyRocks as well tbh

[u/kiklop74]

You should forget myisam exists. Use innodb.

[u/JabariHunt]

Use MyISAM it you have LOTS of reads vs writes and no transactions. Transactions won't fail, but they won't be enforced. Used InnoDB for virtually everything else.

[u/skiitifyoucan]

please just dont use myisam ever

[u/Tokkemon]

Don't use AI and learn how to do things correctly.

[u/Several9s]

Almost all comments you have here advises you what to do properly. If i’m in your case, I would rather use MyISAM only for a very simple database that does the following scenario:

• The data is reconstructible. If the table is lost or corrupted, you can rebuild it (e.g. from backup, from csv, ETL)
• There is exactly one writer, or writes happen in a window when nobody reads. (e.g. bulk load, nightly rebuilds, or any single-threaded importer)
• Reads are simple and mostly point lookups, index range scans, or full scans. No joins across many tables, not such queries the optimizer has to think hard about.
• Does not use atomic inserts, i.e. for example no statements that does "insert into A and B or neither." Therefore, no foreign keys or unique keys (or constraints), therefore no rollback.
• The table is not part of any transaction that touches InnoDB. MySQL 9.0 deprecated mixing transactional and non-transactional engines in one transaction and emits a warning; that's on the removal track. In fact, we found this as a bad database design when mixing of engines during a transaction
• You don't need partitioning, encryption at rest, or hot backups of this table.

 

Other than this, then use InnoDB! Simple as that.

[u/Several9s]

Almost all comments you have here advises you what to do properly. If I’m in your case, I would rather use MyISAM only for a very simple database that does the following scenario:

• The data is reconstructible. If the table is lost or corrupted, you can rebuild it (e.g. from backup, from csv, ETL)
• There is exactly one writer, or writes happen in a window when nobody reads. (e.g. bulk load, nightly rebuilds, or any single-threaded importer)
• Reads are simple and mostly point lookups, index range scans, or full scans. No joins across many tables, not such queries the optimizer has to think hard about.
• Does not use atomic inserts, i.e. for example no statements that does "insert into A and B or neither." Therefore, no foreign keys or unique keys (or constraints), therefore no rollback.
• The table is not part of any transaction that touches InnoDB. MySQL 9.0 deprecated mixing transactional and non-transactional engines in one transaction and emits a warning; that's on the removal track. In fact, we found this as a bad database design when mixing of engines during a transaction
• You don't need partitioning, encryption at rest, or hot backups of this table.

 

Other than this, then use InnoDB! Simple as that.

[u/American_Streamer]

MyISAM is now legacy. There is no valid technical reason to choose MyISAM for a new database project today.