How can MyISAM be "faster" than InnoDB if
- MyISAM needs to do disk reads for the data?
- InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
The only way MyISAM can be faster that InnoDB would be under this unique circumstance
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
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
Since both InnoDB and MyISAM do disk I/O, what random factors dictate who is faster?
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.
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...
In a simple world, MyISAM is faster for reads, InnoDB is faster for writes.
Once you start introducing mixed read/writes, InnoDB will be faster for reads as well, thanks to its Row locking mechanism.
I wrote a comparison of MySQL storage engines a few years ago, that still holds true to this day, outlining the unique differences between MyISAM and InnoDB.
In my experience, you should use InnoDB for everything except for read-heavy cache-tables, where losing data due to corruption is not as critical.
Which is faster? Either might be faster. YMMV.
Which should you use? InnoDB -- crash-safe, etc, etc.