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.

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

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.

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.