109

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?

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    Consider FirebirdEmbedded, it's a very powerful mixture of the two. – Stefan Steiger Jan 28 '11 at 12:03
  • 1
    Voting to close as too broad. Possible duplicate was deleted: http://stackoverflow.com/questions/3630/sqlite-vs-mysql – Ciro Santilli OurBigBook.com Nov 25 '15 at 16:23
  • 17
    It is broad now, but, when posted that sort of question was acceptable, or, at least, tolerated. I hope that the question is not deleted, like the possible duplicate, so that others can still read the discussion and recommendations. – Mawg says reinstate Monica Nov 26 '15 at 08:42
  • 13
    Such a pity that those questions which have a high impact and visibility (look at the number of up-votes below) are being closed by this site's "monitors". Which makes the whole monitoring process and monitors selection quite questionable. –  Oct 10 '18 at 14:16
  • 4
    The standards, values and objectives of the site have change dover the years. "Primarily opinion based" is no longer acceptable. If I wanted to ask this today, I would ask on https://softwarerecs.stackexchange.com/, not here, and would list my requirements for a database and receive recommendations. I understand why my question was closed, but I am still nostalgic for a time when we used to have a [tag:books] tag, and the word "best" in a title didn't mean immediate closure :-/ YMMV – Mawg says reinstate Monica Oct 11 '18 at 06:38

4 Answers4

114

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.

MarkR
  • 62,604
  • 14
  • 116
  • 151
68

The sqlite team published an article explaining when to use sqlite that is great read. Basically, you want to avoid using sqlite when you have a lot of write concurrency or need to scale to terabytes of data. In many other cases, sqlite is a surprisingly good alternative to a "traditional" database such as MySQL.

Josue
  • 896
  • 7
  • 2
12

SQLite out-of-the-box is not really feature-full regarding concurrency. You will get into trouble if you have hundreds of web requests hitting the same SQLite database.

You should definitely go with MySQL or PostgreSQL.

If it is for a single-person project, SQLite will be easier to setup though.

halfer
  • 19,824
  • 17
  • 99
  • 186
Jerome WAGNER
  • 21,986
  • 8
  • 62
  • 77
  • For simplicity's sake, I always have a single piece of software which is the only user allowed to access the database, even for networked apps which use a MySql database, but, obiovusly, especially for Sqlite. – Mawg says reinstate Monica Feb 06 '17 at 13:30
12

My few cents to previous excellent replies. the site www.sqlite.org works on a sqlite database. Here is the link when the author (Richard Hipp) replies to a similar question.

RevanthKrishnaKumar V.
  • 1,855
  • 1
  • 21
  • 34
Maksee
  • 2,311
  • 2
  • 24
  • 34
  • 1
    Thank you for the link, the same author continues with [an own workbench](http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg00464.html) he tested over an Athlon 1600. 1GB RAM. 5400RPM IDE disk. Redhat 7.3. He concludes: «I now believe that SQLite is appropriate for use as the primary database on websites that get up to 1 million hits per day.» – Rutrus Dec 27 '16 at 00:45