Your ads will be inserted here by
Easy Plugin for AdSense.
Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.
Why is innodb inserts slower than MyISAM
Innodb VS MyISAM inserts and bulk loads
mysql> use testdb;
Database changed
mysql> set profiling=1; INSERT INTO `myisam` (`myisam`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000012 |
| checking permissions | 0.000002 |
| Opening tables | 0.000004 |
| System lock | 0.000002 |
| Table lock | 0.000001 |
| init | 0.000004 |
| update | 0.000028 |
| end | 0.000002 |
| query end | 0.000001 |
| freeing items | 0.000011 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)
mysql> set profiling=1; INSERT INTO `innodb` (`innodb`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.03 sec)
+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000003 |
| Opening tables | 0.000019 |
| System lock | 0.000003 |
| Table lock | 0.000002 |
| init | 0.000008 |
| update | 0.000031 |
| end | 0.000001 |
| query end | 0.000002 |
| freeing items | 0.026714 |
| logging slow query | 0.000003 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)
Your ads will be inserted here by
Easy Plugin for AdSense.
Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.
# Lets disable ACID compatibility feature on Innodb OFF
mysql> set global innodb_flush_log_at_trx_commit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set profiling=1; INSERT INTO `myisam` (`myisam`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000003 |
| Opening tables | 0.000023 |
| System lock | 0.000003 |
| Table lock | 0.000002 |
| init | 0.000009 |
| update | 0.000027 |
| end | 0.000001 |
| query end | 0.000001 |
| freeing items | 0.000013 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)
mysql> set profiling=1; INSERT INTO `innodb` (`innodb`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000004 |
| Opening tables | 0.000008 |
| System lock | 0.000002 |
| Table lock | 0.000002 |
| init | 0.000009 |
| update | 0.000032 |
| end | 0.000002 |
| query end | 0.000001 |
| freeing items | 0.000017 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)
# Lets turn that back again to leave system as it is:
mysql> set global innodb_flush_log_at_trx_commit = 1;
Query OK, 0 rows affected (0.00 sec)
So, We could make certain insert’s, updates or inload, bulk load faster in innodb upto x4 times doing this little tweak.
In certain condition it would be even better doing this as permanent solution while compared to MyISAM since this innodb would give other good features
such as row based lock, RDBMS features such as fk’s.