SQLite vs MySQL

Question

SQLite is a flat-file database and MySQL is a normal database. That's great but I'm not sure which is faster where or better for what? What are the pros and cons of each option?

Answer 1

SQLite is great for testing and prototyping, or for embedding in applications. MySQL is worthy of (large scale) production environments.

This site has guidance on when to use SQLite

Here is my personal summary:

SQLite:

• easier to setup
• great for temporary (testing databases)
• great for rapid development
• great for embedding in an application
• not fit for production (in large scale)
• doesn't have user management
• doesn't have many performance features
• doesn't scale well.

MySQL:

• far more difficult/complex to set up
• better options for performance tuning
• fit for a production database
• can scale well if tuned properly
• can manage users, permissions, etc.

Answer 2

Check this link. I think it is quite resonable and it is hard to add anything more.

Answer 3

From personal experience, I'd say SQLite is production worthy, just not when you're running a web site like stack overflow 8^D

I have two applications that use SQLite for the primary data source since they are database style applications. It is FAR easier to deploy this than the Microsoft equivalent and providing updates is as simple as zipping up the file and having the user download and unpack it.

In addition, you can use it for serializing basic objects without the hassle of versioning/updates. I will admit part of my dilemma most likely stems from taking my first crack at things, but I had developed a custom object I wanted serialized to a file, followed all the recommended norms, and then had my application not be able to read previous versions when I added a new field. With SQLite, you can modify to your hearts delight, and not break anything.

Answer 4

Justin's answer seems to be evaluating from the perspective of a multiuser app (and is a good evaluation). It's good to note though that sqlite has a lot of "single user" production applications. By going single user you get rid of the security and concurrency issues. This allows you access to data via SQL without the overhead of running a server. In practical terms, they are great for "personal databases". Adium X, the sorta-pidgin-port for Mac OS X uses sqlite for its chat logs. I've not personally confirmed this, but my understanding is that the "awesome bar" in Firefox 3 is implemented using sqlite. Also, Mac OS X has an entire data storage API that's built on top of sqlite (which, now that I think about it, is probably why Adium X is using it). I believe the security issues are addressed at the OS layer (unix file permissions, etc).

So, while sqlite is not appropriate for large multiuser production applications, it works quite well for single user production apps.

Answer 5

SQLite is being use a lot in client-side data stores: Firefox uses it extensively, various apps Apple wrote for the iPhone use it, yum on Linux was rewritten to use it. It's probably more flexible (especially in data structures and indexing) and easy to use than the Berkeley DB's and custom binary formats that some of these things previously depended upon.

All those things have something in common: only one process/thread will probably want to write to the database at a time, and a relatively small number of things are going to want to read from it. SQLite blocks all other IO on the table during a write, which isn't so much for multi-user/multi-threaded the whole table when you start doing an update.

If you prototype with SQLite, be careful. It's "weakly typed" by default -- you can put a string into an integer column unless you enable strict affinity mode and I'm not sure if that's been implemented yet.

Answer 6

To echo what @jaredg said - SQLite won't handle multi-user or even multi-threaded use since writes lock the database. That means you can't even read from the database while it's being updated. More on my experiences with it here.

Answer 7

But SQLite handles multi-user fine if all you are doing is reading. Reading does not require a lock. So SQLite can run any well trafficed site/app that doesn't require modifying records. Or where 1 person is doing the editing. Like a blog with no comments.

It's small, available on all platforms and free. Also open source, and the code is well documented. So change what you want. I think SQLite is fit for mass production. Look at Firefox, iTunes, etc, etc.

And to the OP: Compared to any other SQL server MySQL is easy peasy to set up. I mean com'on, on Windows you install answer a few questions and you off. Pretty much the same on Mac or any Linux destro.

Answer 8

There is an excellent interview with D. Richard Hipp, creator of SQLite, on FLOSS Weekly. In this interview, he discusses when, and when not, to use SQLite among many other things.

FLOSS Weekly 26: SQLite

Answer 9

It seems for a huge majority of sites using MySQL, SQLite would be more than adequate. It just seems to be a mindset that "if it's anything resembling production, I have to use MySQL!"

I would say that if you don't have to do any performance-fiddling with MySQL, you can get away with using SQLite..

Answer 10

Another difference: SQLite supports transactions without the overhead of InnoDB. I would consider SQLite for a website running on a VPS with very little memory.

Answer 11

We heavily use both SQLite and MySQL in production.

The SQLite databases get used where we have a large amount of mostly read-only data. We build this datasource from a large number of flat files held in our subversion repository, and then distribute copies of the data to production nodes which require access to it.

Profiling SQLite is much trickier than with MySQL - particularly if you're wanting to get data from your production nodes. This is something you'd have to do in your application. It's also less than straightforward to have SQLite's query planner tell you what it's going to do with certain queries, which makes optimisation tricky.

Answer 12

I am building a simple logging app that will store logs with tags and be private to one user. I plan to use other means for reporting and search, Sphinx indexes, Hadoop, etc. I am about to pull the trigger on using sqlite for the db because then the client and server side db can be exactly the same. I can mail users copies of their db file if things go wrong. I am hoping that I can roll the sqllite logs over if the data gets too huge. I am also wondering if it's possible to index the sqllite files themselves without having to export the data via xml to Sphinx.

Answer 13

I use sqlite as development db for website and then deploy to mysql on production. This is easier to setup and since data are stored in simple flat file you can copy / move them like you want (great when you try to make major structure change but want revert back option).

I also use sqlite as desktop apps file save format for anything that look/sound/smell like "save"/"save as"/"load"/"import"/"export".

Answer 14

mysql vs sqlite r 2 plataforms por diferentes applications, sqlite is perfect por standalone apps or databases and querys veri ligth, and mysql is pefect for client-server apps more complex apps

Answer 15

For Php use:

Mysql is good if you perform often INSERT or UPDATE queries. sqlLite is better for SELECT queries (file access is always better), moreover, you can store your sqlite DB in memory! Very very effective and very good in production!

I am working on this project: "We run a Java program to collect some news coming from RSS, let's say 10 000 news. Every night, at 00:00, we store these news in a sqlite. Then we only perform READ queries to the sqlite, it's fast, easy, simple, scalable bcoz we simply copy/paste the .db file on severals server!

My conclusion is to use sqlite like a read only cache.

For client application use:

sqlite is really nice bcoz you have a real DB (SQL queries) w/o a server running on the user's computer. Before, I remember having used in Visual Basic ini files or reg DB, hu, dirty!

Answer 16

Despite the various answers here, it seems to me that the balance has changed slightly when SQLite introduced WAL mode. From what I have been able to discover, this allows simulataneous updates without getting (as much) lock contention.

The big downsite of sqlite has been that during a transaction that involves updates to the database the entire database is locked rather than the much finer grained locking of other databases. With WAL mode, each user is effectively able to see a consistent view of data, even if other people are writing to the database - and therefore the locks that sqlite applies can be applied less frequently.

The documentation about when the WAL is re-encorporated into the main database is not as clear as it might be and it turns out that that the last connection to close will write it back (as well as the other mechanisms provided). In a scenario where the sqlite database is supporting a web site, provided there is a time when there is not a web page request in progress, the wal gets re-incorporated. The 100K hits/day figure gives about a 1.15 sec per hit, so unless the database uses lots of queries per page, at the end of most page requests, or just after a burst if that is how they come, the WAL will be written back. That is of course if it needs to be - most hits are likely to be of a read only nature.

The other thing that seems to be important with a sqlite based web site is to ensure all the queries are encased in a single transaction covering the entire page display. Some tests on my desktop computer showed about 7 inserts per second when each one was a transaction and 1000/second when they were all encased in a single transaction.

With the above caveats about what slows SQLite down - the upside is that its a library, with the code running in the process that calls it, compared to mysql where there is an interprocess communication for each sql statement. This should make sqlite faster especially when complex joins etc are done in code rather than sql.

What I really like is the simplicity of backup up and restoring the database. It is slightly simplistic to say you just need to copy the file, since a transaction may be going on when you are doing that - but there is a backup api which is used by the command line utility

sqlite3 /path/to/live.db '.backup /backup/path.db'

to get a consistent snapshot in the cases where you can't stop the processes doing the updates.