Why Are MySQL InnoDB Inserts So Much Slower Than MyISAM Inserts?

Why MySQL InnoDB Inserts Are Significantly Slower than MyISAM Inserts

The Problem:

When inserting large random numbers as keys into a table, MySQL InnoDB exhibits significantly slower insertion speeds compared to MyISAM. For example, inserting 1 million rows into a 10 million-row table takes 6 seconds with MyISAM but 3433 seconds with InnoDB.

The Cause:

The primary reason for this disparity lies in InnoDB's transaction support mechanism. Unlike MyISAM, which stores data directly on disk without any intermediate buffering, InnoDB performs a log flush to disk for every insert statement. This commit-on-each-statement behavior significantly impacts performance.

The Solution:

To optimize InnoDB insertion speed, it is recommended to use explicit transactions. By encapsulating the insert statements within a transaction, you can defer the commit operation until all statements are executed. This approach dramatically reduces the number of required disk flushes, thereby enhancing insertion speed.

To apply this solution, execute the following commands before and after your insert loop:

Before the insert loop:

START TRANSACTION;

After the insert loop:

COMMIT;

By implementing this technique, you can significantly accelerate InnoDB insertion performance and obtain comparable speeds to MyISAM, while still benefiting from InnoDB's transaction support and data integrity features.

The above is the detailed content of Why Are MySQL InnoDB Inserts So Much Slower Than MyISAM Inserts?. For more information, please follow other related articles on the PHP Chinese website!