Mass inserts, updates: SQLite vs MySQL (update: delayed inserts)
Lessons learned: SQLite performs inserts 8-14 times faster then InnoDB / MyISAM SQLite performs updates 4-8 times faster then InnoDB and as fast as MyISAM SQLite performs selects 2 times faster than InnoDB and 3 times slower than MyISAM SQLite requires 2.6 times less disk space than InnoDB and 1.7 times more than MyISAM Allowing null values or using synchronous=NORMAL makes inserts 5-10 percent faster in SQLite Using SQLite instead of MySQL can be a great alternative on certain architectures. Especially if you can partition the data into several SQLite databases (e.g. one database per user) and limit parallel transactions on one database. Replication, backup and restore can be done easily over the file system. The results: (MySQL 5.6.5 default config without binlog, SQLite 3.7.7, PHP 5.4.5, 2 x 1.4 GHz, disk 5400rpm) insert [s] sum [s] update [s] size [MB] JSON 1.84 1.30 2.92 2.96 CSV 1.97 2.25 3.7 2.57 SQLite (memory) 2.74 0.12 0.52 0.00 SQLite (memory, not null) 3.00 0...