How much faster is MyISAM compared to InnoDB?

People say InnoDB is not 开发者_如何学运维so fast like MyISAM. But how much slower? Just as a rule of thumb in the wind, of course. I mean... is it usually 0.5x as fast as MyISAM? or even worse? Or does the average visitor not recognize any temporal difference when surfing an MyISAM platform vs. same thing with InnoDB?

I'd be very surprised if you noticed any practical real world difference unless you're running a very large/resource intensive web site.

There are some (albeit quite old) benchmarks out there (here for example), but your experience will obviously differ depending on your specific usage pattern.

It can be a big difference that depends on how you use the database in question. For example - select count(*) queries are cheap when run on a myisam table but (can be) really slow when run on an innodb table. Also a lot of people don't configure their installation for innodb performance - especially if they're used to myisam which performs pretty well on a rather default mysql installation.

My advice would be to run your own tests by taking a couple of top queries and running them against myisam tables and then innodb versions. Also head over to http://www.mysqlperformanceblog.com/ for tips on how to tune innodb to get the most out of it.

This is a grand generalization, but you must understand that when using InnoDB the db is constantly checking constraints, the actual performance hit will vary according to how many contraint checks it has to make on Inserts and updates. you should also have into account the distribution of queries on your db, 90% selects, 10%insert/update queries. a 10% performance hit con inserts, isn't particularly big. Now, if your DB is write intensive, you might consider using MyISAM. Remember, MyISAM is not ACID compliant, so you've no guarantee on data integrity, this means no foreign key checks, nor transactions.

Cheers

Depending on your load pattern MyISAM may not be faster at all. For instance, InnoDB can do row-level locking while MyISAM only supports table-level locking. This particular feature can give InnoDB a significant advantage under load that involves many concurrent updates to different parts of the same set of tables.

So you cannot really generalize. Instead you most figure out what load you are going to have, and then research the topic from this angle. Or at least just set up a simple simulation/benchmark and see how things perform.