When MyISAM is faster then InnoDB?

MySQL & MariaDB Other MySQL® Questions
1

Hello,

All recent tests that I’ve found suggest that InnoDB outperforms MyISAM in almost all cases. Still, it’s said that MyISAM is better for case scenarios where selects are vast majority (but it’s not confirmed in tests). So when MyISAM is really better? (let’s assume that there will be only ‘select’ queries)

Regards,
Lucek

2

I haven’t ever tested any of this myself, so I’d really be interested in checking out these test results you’ve found. If you could post some links, that would be great.

I mostly work on analytics and reporting stuff, which is largely read only (except during data loading). Many of my earlier work was using MyISAM, but recently I’ve been using InnoDB, in part to have better crash recovery.

Speaking purely theoretically, since I’ve never conducted any of these tests, I’d say the big read-only performance differences between MyISAM and InnoDB seem to be caused by: 1) table size, 2) data caching, 3) use of clustered indexes. How much these will impact your performance tests depend entirely on the queries, data and configuration involved.

First off, InnoDB tables are supposedly 3x the size of MyISAM tables. So if your test is a table scan against a “cold” server, then MyISAM should win hands down every time.

The second point is data caching. InnoDB can cache both index and data pages in memory, while MyISAM only caches indexes. If your test database can fit into memory, then on a “hot” server, InnoDB should outperform MyISAM.

The third is the use of clustered indexes by InnoDB. A PK lookup of an entire row is less expensive for InnoDB because the index leaf page is the data page. MyISAM requires another random read once it has retrieved the row ID from the index. So for random PK reads on a “cold” server, InnoDB should outperform MyISAM.