Do InnoDB vs MyISAM comparisons still hold true in terms of speed of reads vs writes? [closed]

Question

I know there are a bunch of other questions on this. The problem I'm having is finding an up to date consensus on the matter with regards to the latest versions of the storage engines.

Some people have said that for reads MyISAM is faster, but 'recent' improvements in InnoDB have either alleviated or eradicated this difference. Is that the case?

This article at MYSQL Performance Blog gets linked to a lot, both directly and from other articles that have been linked to from questions on here, but it's 3 years old. We're developers, we practically work on dog years - 3 years is an AGE!

--

I have a number of tables, some of which are mostly written to and some of which are mostly read from and hardly ever written to. There's also a lot of foreign keys which require InnoDB anyway so those will stay as InnoDB, but I would like to know if I'm right to change the tables that are mostly read from and have no foreign keys to MyISAM, or whether this is a pointless thing to do, with the latest versions of the two storage engines being used?

--

I appreciate that this may be voted to be closed, but if doing so please you could link to an up to date article? I've already tried a Google search restricted by timeframe but the articles linked to the same older articles. Thanks.

Overcash

Answer 1

AS you point out, the article on MySQL Performance Blog is almost 4 years old now. Since then InnoDB was improved a lot. MyISAM was not.

Just yesterday MySQL 5.5 was released. It is the first version where InnoDB is the default storage engine. For a reason. Oracle claims as much as 3.5 performance gains for Linux and up to 15 times performance gains for Windows (compared to 5.1 InnoDB).

Mechanics

Answer 2

I would say, there there is almost no difference. I read an article recently, that dispelled this myth, but sady, it doesn't seem to be working anymore.

Anyway, I do believe that the descision between MyISAM and InnoDB now rests mostly on the questions

• Do you need to work with foreign keys?
• Do you need the table level or row level locking.

At least, these are the principles I base my choices upon.

Oxus

How about ACID transactions? I'd say this is the main reason behind choosing InnoDB. – Mechanics

Since I'm usually working with Web apps, this is usually not a big concern, but Yes, that's a great point! If you need ultimate reliability and consistency, InnoDB is probably your best bet. Thanks Mchl for pointing that out! – Oxus

Why would you need table level locking as opposed to row level locking? Could you give an example when that would be the case? In my view InnoDB is almost always the best choice. Except when you need full text search. – Idaline

No, I can not. But that is mostly because I can't imagine anything like that. There are however quite a lot of imaginative people out there! :) – Oxus

You need table level locking, when you're dropping/recreating indexes on a production server :D But hey... it's not something you should do anyway ;) – Mechanics

I told you there were creative people! :) – Oxus

innodb - clustered indexes - no contest - end discussion – Indefeasible

Answer 3

In general it seems as though the concensus is to primarily use InnoDB but there are still some areas in which MyIsam is much faster than InnoDB. I have a website that lists times for Running Races and creates a record for each athlete www.marastat.com. Our site has in the million(s) range of athletes. We added a search feature and did a like search on the first/last names and found that using InnoDB and an Index it took over a minute for some searches to complete. We have made a duplicate of the InnoDB table and added full text indexes on the MyIsam version and most queries can complete in a second or two.

Cyprinid