Is there a speed difference between sqlite3 and mysql?

Question

After much googling I have been wondering what the benefits/differences are between mysql and sqlite3. I am primarily hoping for a speed bump when moving my development database to my production database, although I imagine this will primarily happen in my code. Does any know what the major differences are in performance? ARE there? Or would you be able to point me in the direction of some literature comparing the two?

Answer 1

I'm hoping that from your question, you aren't talking about using a different database system in development and production.

If you are using a different DB in development and production, DON'T. Different database engines behave differently and will cause unexpected bugs to appear. Try to get your development system as close to production as you possibly can (hint: run it in a VM)

SQLite and MySQL are both fine products in the right context. Understand what their capabilities are.

SQLite:

• Runs in-process with the client application
• No network capability
• Very low query overhead because of this
• Potentially rather fast for easy queries because there is less IPC, system calls and data copying to do.
• Very limited concurrency (I think it's either one writer or multiple readers, per database file)

MySQL:

• Far more features
• Choice of engines (InnoDB provides good features for many workloads)
• Much more control of storage, backup, restore etc
• Network-capable - which allows its use in large scale applications
• Better concurrency - the InnoDB engine has MVCC which means that readers do not block writers, nor do writers block readers. Even the old MyISAM engine has table-level locking rather than for the whole database as sqlite.
• Probably a better optimiser for complicated queries

In short, you can't really compare them like-for-like. SQLite is quite popular as an embedded database - Firefox 3 has one inside it.

In SQLite, you're stuck on storing your database on a local disc (or of course, network disc, if one is available). If you want to scale your web application, you'll probably have to use a server-based database such as MySQL.