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?

Improve this question
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, 2014 at 13:01
  • Try SET AUTOCOMMIT=0 for the batch. I'd suggest reading most of the pages linked from dev.mysql.com/doc/refman/5.7/en/innodb-performance.html and particularly dev.mysql.com/doc/refman/5.7/en/….
    – dartonw
    Jun 30, 2014 at 22:55

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.