0

MyISAM vs InnoDB

I keep reading how InnoDB is faster than MyISAM. I wanted to find out myself.

When I was using a forloop in PHP to fill my MySQL test database with 100,000 numbers using PDO driver.

|a|
+-+
|0|
|1|
|2|
|3|
|4|
...
|99999|

I find out that with InnoDB it went 95 records per second. After a while I stopped my script cause going to 100,000 is just going to take too long. Changing my table to MyISAM and re-running the script made a whopping 6000 records per second! Both tables have no indexes.

How is it that the internet is claiming InnoDB is faster than MyISAM. Could it be that PDO is bottlenecking InnoDB?

tombom
  • 3,158
  • 1
  • 20
  • 28
Shams
  • 3
  • 2
  • While I could give you a generic response, it would be better if you specified PHP/driver version and MySQL server version, and OS/php/database configuration, as there has been significative changes on InnoDB in terms of default configuration and performance in the latest versions.

    The generic answer is that you are probably using everything by default, and in that small table, MyISAM writes mostly to filesystem cache (memory) in an unsafe way and InnoDB does a disk fsync for every single statement.

     – jynus Jun 30 '14 at 13:01
  • Try SET AUTOCOMMIT=0 for the batch. I'd suggest reading most of the pages linked from http://dev.mysql.com/doc/refman/5.7/en/innodb-performance.html and particularly http://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-transaction-management.html. – dartonw Jun 30 '14 at 22:55

1 Answers1

0

This is often one of the most mythical subjects of MySQL. To separate fact from fiction, you need to remember the strengths and weaknesses of each Storage Engine. Each Storage Engine and application will govern the workload, read I/O, write I/0, and tuning options.

I have written many posts about how and when to pick one storage engine over the other:

I have many more posts but you can start with these.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520