Fast: MyISAM or InnoDB

The only way MyISAM can be faster that InnoDB would be under this unique circumstance    

MyISAM: When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this:

I wrote about this in my past posts: Best of MyISAM and InnoDB (Please Read This One First)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Optimized my.cnf for high-end and busy server (Under the heading Replication)
Which DBMS is good for super-fast reads and a simple data structure? (Paragraph 3)

OK, what about InnoDB? Does InnoDB do any disk I/O for queries? Surprisingly, yes it does !! You are probably thinking I am crazy for saying that, but it is absolutely true, even for SELECT queries. At this point, you are probably wondering ""How in the world is InnoDB doing disk I/O for queries?""

It all goes back to InnoDB being an ACID-complaint Transactional Storage Engine. In order for InnoDB to be Transactional, it has to support the I in ACID, which is Isolation. The technique for maintaining isolation for transactions is done via MVCC, Multiversion Concurrency Control. In simple terms, InnoDB records what data looks like before transactions attempt to change them. Where does that get recorded? In the system tablespace file, better known as ibdata1. That requires disk I/O.

COMPARISON: Since both InnoDB and MyISAM do disk I/O, what random factors dictate who is faster?

Size of Columns
Column Format
Character Sets
Range of Numeric Values (requiring large enough INTs)
Rows Being Split Across Blocks (Row Chaining)
Data Fragmentation caused by DELETEs and UPDATEs
Size of Primary Key (InnoDB has a Clustered Index, requiring two key lookups)
Size of Index Entries
the list goes on…
Thus, in a heavy-read environment, it is possible for a MyISAM table with a Fixed Row Format to outperform InnoDB reads out of the InnoDB Buffer Pool if there is enough data being written into the undo logs contained within ibdata1 to support the transactional behavior imposed on the InnoDB data.

CONCLUSION:Plan your data types, queries, and storage engine real carefully. Once the data grows, it might become very difficult to move data around. Just ask Facebook…

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>