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.

4 comments:

Normally I would ask the following methodology-related questions to someone conducting performance tests like this:

Are these claims anywhere near valid when applied to 13 table join selects with correllated subqueries? How about on a system 4x as powerful with a RAID 10 setup? What if you tune various settings and create optimal indexes?

However, as you have done us the favor of including your source code with your claims, anyone at all serious about databases can easily discover that your testing methodology is flawed on an even more simple basis than that.

Why use a database if all you're going to do is flat inserts and selects, why not use a data structure more suited to that? SQL databases are for transactional requests on highly structured data, not simple storage and retrieval of mundane data.

Joins and subqueries are not part of this article.Having huge tables, you normally try to avoid any joins and subqueries. Adding indexes makes inserts slower.The write rate for inserts is about 1 MB/s, so IO is not relevant here.

Thank your for the test. I'ts excactly what I'd discovered.No doubt, MySQL has its area of application. But SQLITE is very nice also and in some cases the better choice.I've worked with Huge Tables on MySQL. but now I've migrated to SQLITE3. In my case, the Database doesn't require more than one user simultaneously. The Job is only data matching and converting. I was suprised about that SQLITE3 performs many faster than MySQL. Because of this circumstances and because of the easy handling I will keep an eye on SQLITE.