Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 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 at 22:55

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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