MyISAM vs InnoDB for BI / batch request performance (i.e. _NOT_ OLTP)

Question

Of course, for a transactional database, InnoDB is a slam dunk. MyISAM does not support transactions or row level locking.

But what if I want to make big nasty batch requests that concern hundreds of millions of lines?

Are there areas where MyISAM has a relative advantage over InnoDB ??

for example, one (junior) that I know ... "select count (*) from my_table;" MyISAM knows the answer to this instantly, while InnoDB may take a minute or more to make a decision.

--- Dave

Answer 1

MyISAM scales with very large datasets. InnoDB outperforms MyISAM in many situations until it can store indexes in memory, and then performance drops sharply.

MyISAM also supports MERGE tables, which are a kind of "poor person." You can add / remove very large datasets instantly. For example, if you have 1 table per quarter, you can create a merge table for the last 4 quarters or a specific year or any range that you want. Instead of exporting, deleting, and importing data for data transfer, you can simply update the main contents of the MERGE table. No code change is required as the table name does not change.

MyISAM is also better suited for logging when you are only adding to the table. Like MERGE tables, you can easily swap (rotate "logs") to a table and / or copy it.

You can copy the DB files associated with the MyISAM table to another computer and simply put them in the MySQL data directory, and MySQL will automatically add them to the available tables. You cannot do this with InnoDB, you need to export / import.

These are all specific cases, but I have used each of them several times.

Of course, with replication you can use both. The table can be InnoDB on the master and MyISAM on the slave. The structure should be the same, not the type of table. Then you can get the best of both. The BLACKHOLE table type works this way.

Answer 2

Here's a great article comparing various performance points http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ - you'll have to evaluate it from several angles, including how you intend to write your queries and what your circuit looks like. This is simply not a black and white question.

Answer 3

According to this article, starting with version 5.6, InnoDB has been designed in such a way that it is better in all scenarios. The author is probably a little biased, but it clearly describes which technologies are seen as the future direction of the platform.