sqlite and mysql

Recently I am using sqlite for database and my first impression is that this software is less flexible in the query than some array processing tools (I am using numpy). I searched internet for several possible database softwares, ie., sqlite and mysql. Here is a summary of my searchings.

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


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.

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.

These are from http://stackoverflow.com/questions/264457/is-there-a-speed-difference-between-sqlite3-and-mysql.