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.

Answer 2

Here's a simple way to look at it: Sqlite is Notepad and MySQL is MS Word. Both do basically the same thing, but are very, very different at their core an are used for different purposes.

Sqlite is a good database, and often good enough, while MySQL is a much more complex system, but with that complexity comes power.

I'd also have to recommend against using different databases in development and production. It's silly to use something different to what your customers are using. If you need the speedup while developing, your customers could use the same speedup while they're using your program.

Answer 3

SQLite is the fastest

SQLite can be only access by one connection, use the file system to store everything and doesn't use any server.

It is fast and lightweight. Actually supposed to be twice as fast as MySQL.

SQLite and MySQL are not used for the same tasks

You will use SQLite for simple apps than will not need to store more than 5 Go of data. E.G : a local agenda, a music player, mocking or even for a web site demo that you will embed in a USB key.

Mysql, or else, need to be choosen for bigger projects because it can handle a huge set of data and be access concurrently. You'll use it for Web sites with user accesses, intranet, etc.

Answer 4

SQLite is an embedded database engine, but it runs in the same process as your application. MySQL, is a database server that runs in its own process.

SQLite doesn't waste processing and bandwidth packing up requests between the application server process and the database server process. It simply parses the requests, figures out what to do (query plan), and calls an fopen() on the SQLite database file and executes the query.

MySQL is better when your site is getting hammered with lots of concurrent requests, and you need the extra threading and queueing to serve all of the requests properly.

SQLite is probably the best database to use if your site does not get more than 100000 hits per day, or your database size doesn't exceed about 25 GBytes. The other advantage of SQLite is that you can often use it easily in situations where extra processes or database servers are scarce. Check out http://www.sqlite.org/whentouse.html.

You can also do other stuff with SQLite. Check out http://www.squidoo.com/sqlitehammer for other insights into SQLite.

Answer 5

This may be different in different languages, but in PHP SQLite is good if you're only sporadically modifying the database. This is because in order to ensure integrity, any time the database is to be modified the SQLite file is locked, the changes are made and written to the file, and the file is then unlocked. During this time the database cannot be accessed by any other process, and it's implementation dependent whether or not requests from other processes fail or block during that time.

Answer 6

Actually I found myself with the need to reproject database logic of my program (already in production) because of a bug of SQlite that when is facing a relatively large amount of data and operations, it simply fails locking down the database and leaving it so until the program shuts down, meaning that every program data progress is temporarily, then every add is lost. Cute uh?

Migrating to MySql, and I get to the point, I tried something to test speed difference, and came out that something like a safe query as "UPDATE table SET column1=column1" on a table of 280'500 records, SQlite required about 13 seconds, while MySql took just 1 sec.

I'm still not a db guru, but to choose is enhough a simple consideration: if you require a simple memory storage for limitated amount of data,complexity and concurrency (such as a small utility or programs operating with no-critical data) then SQlite will be fine, otherwise when you need stability, concurrency, large amount of data, complexity in queries, then your db will be MySql.

About different databases in develop and production I don't agree, but if you did so, you have for sure your good reasons.