Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

3 Answers 3

up vote 31 down vote accepted

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:

ALTER TABLE mytable ROW_FORMAT=Fixed;

I wrote about this in my past posts

InnoDB

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...

share|improve this answer
1  
Excellent answer, Rolando. I have to question your inclusion of the incredulous claims made by Michael Stonebreaker, who is simply trying to sell his own product and knows nothing about Facebook. Having listened to Facebook present about MySQL several times, it is clear that they are comfortable with their choices. –  Aaron Brown May 4 '12 at 1:22
    
@AaronBrown I have listened to Harrison Fisk last year at Percona Live NYC and you are right -- Facebook is very happy with their exclusive use of InnoDB and how they spend time coming up with ways to do online schema change system wide. He even offer the audience the chance to work for Facebook handling big data. I included the article to show that some have fears about that. I would welcome the chance to work with huge data. It would be fun and challenging. Imagine the techniques there are to learn. Of course, I would never touch MyISAM for the rest of my life... –  RolandoMySQLDBA May 4 '12 at 2:09
    
I was also at that conference (and had the good fortune to be able to give a talk) & Harrison's presentation was excellent. –  Aaron Brown May 4 '12 at 2:57
add comment

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.

share|improve this answer
    
Thanks a lot, I'll take a look –  jcho360 May 18 '12 at 12:04
add comment

Which is faster? Either might be faster. YMMV.

Which should you use? InnoDB -- crash-safe, etc, etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.