Sqlite or MySql? How to decide?
Any good rules of thumb on how to decide which of the two to use?
And, if you take over an Sqlite database, and the system is expected to "get much larger", how to decide whether to stick with it or move to MySql?
Answer
Their feature sets are not at all the same. Sqlite is an embedded database which has no network capabilities (unless you add them). So you can't use it on a network.
If you need
- Network access - for example accessing from another machine;
- Any real degree of concurrency - for example, if you think you are likely to want to run several queries at once, or run a workload that has lots of selects and a few updates, and want them to go smoothly etc.
- a lot of memory usage, for example, to buffer parts of your 1Tb database in your 32G of memory.
You need to use mysql or some other server-based RDBMS.
Note that MySQL is not the only choice and there are plenty of others which might be better for new applications (for example pgSQL).
Sqlite is a very, very nice piece of software, but it has never made claims to do any of these things that RDBMS servers do. It's a small library which runs SQL on local files (using locking to ensure that multiple processes don't screw the file up). It's really well tested and I like it a lot.
Also, if you aren't able to choose this correctly by yourself, you probably need to hire someone on your team who can.