1

I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion.

MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf.

And tables are created with no indexes.

Insertion are done using statements like

INSERT INTO Table (columnA, columnB, columnC) VALUES (?, ?, ?)..." 
(10,000 (?, ?, ?)s) 

db.Exec(stmt, valueArgs...)

with valueArgs being the actual VALUES It is widely believed that MyISAM is faster than InnoDB in insertion.

Baron Schwartz, author of the book High Performance MySQL, explained why in this post Mysql: Insert performance INNODB vs MYISAM.

However, in my experiment, I found out that InnoDB is actually faster than MyISAM. Could anyone give me some insights?

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper
JYOTI RAJAI
  • 866
  • 4
  • 11
Bruce
  • 21
  • 2
  • 4
    InnoDB has undergone many improvements over time, including performance enhancements, and that question you linked is 8 years old. As for MyISAM, while still available its use is deprecated and so doesn't receive nearly as much development attention. Are you looking for something more than that? – Willem Renzema Jun 05 '20 at 01:44
  • @WillemRenzema Hi, thank you for your valuable insights. I was just doing an experiment and was expecting that MyISAM performs better in bulk insertion than InnoDB. I would like some more deep insights. – Bruce Jun 05 '20 at 02:55
  • 1
    I'd guess (stress guess) that if you went back to version 5.5 of MySQL (or before...), you'd find that MyISAM would perform better than InnoDB - as @WillemRenzema says, InnoDB is still being seriously worked on - whereas MyISAM is being deprecated - so it's hardly surprising that InnoDB is overtaking it in areas where it traditionally performed better - it may well still perform better under some circumstances, but they will become fewer and fewer as time goes on. You might like to look at MariaDB's Aria engine which is a continuation of MyISAM which is under active development? – Vérace Jun 05 '20 at 03:39
  • or just enjoy fast Innodb with all its other benefits. – danblack Jun 05 '20 at 04:22
  • @Vérace Thank you for your insights. – Bruce Jun 05 '20 at 05:17
  • @danblack Haha. The experiment needs some explanation, you know. – Bruce Jun 05 '20 at 05:18
  • In fact, InnoDB can't be created without index at all. If no column been used explicitly as PK then InnoDB engine will create a hidden UNSIGNED BIGINT NOT NULL AUTOINCREMENT column and use it as PK for you. – Kondybas Jun 05 '20 at 11:23
  • @Kondybas Thanks for pointing it out. I didn't elaborate well. What I mean is no secondary index. – Bruce Jun 06 '20 at 14:09
  • @Kondybas - It's actually a 6-byte number, doled out in chunks of 256. And it does not work quite like auto_inc. – Rick James Jun 09 '20 at 03:37
  • Upvote -- because you are providing independent proof that the "old wive's tale" is out of date. – Rick James Jun 09 '20 at 03:39

1 Answers1

1

InnoDB has been faster than MyISAM at everything for at least a decade. This shouldn't be a surprise in 2020. There hasn't been a compelling case for using MyISAM in a very long time.

On a workload that involves any concurrency, the difference will be even bigger.

Also, note that Baron's post is from 2012 - and it was probably borderline in terms of accuracy even back then.

Gordan Bobić
  • 1,054
  • 5
  • 12