SQLite vs MySQL

By | December 6, 2012

Today I got asked from a friend about the differences between SQLite and MySQL and in which cases it is better to use the first or the latter one. You can’t really compare these two, I will try to explain why this is the case and when it is better to use the first or the latter.

SQLite is a single-file based database which is useful for testing and for embedding in applications. This means that all the information is stored in a single file on a file system and you use a SQLite library to open this file to read and write your data.

MySQL is a database server which got different forms to store its data (engines) and will server the data to request comming from applications who connect to it.

SQLite:

  • Fast setup. You only need to include a library into your app.
  • Embedded. No need to think about connections
  • Easy Testing. Many frameworks for example Rails use SQLite as testing database
  • No scaling
  • No users and permissions. Everyone who can access the file can access all data.
  • No performance improvements. Sqlite doesn’t cache queries, optimizes selects, etc

MySQL:

  • Scaling. MySQL can easily grow if the application using it needs more. It can even run in a cluster environment
  • Better options for performance tuning. MySQL can be configured to fit to the requierements of your application
  • Fit for a production database
  • User permissions. You can create permisions for different actions on databases, etc.

5 thoughts on “SQLite vs MySQL

  1. Mark Daems

    Just 2 annotations to this general comparison (which is clear and to-the-point, BTW).
    - mysql can be embedded using libmysqld. This offers the advantages of sqlite with full ‘query compatibility’ to mysql as a server. When using libmysql as the programming interface you can just replace the library (and some connection settings) to switch between embedded and client-server. Unfortunately, I have no idea if libmysqld is still maintained and/or distributed by oracle in more recent editions of mysql
    - encryption is available in some sqlite editions. So the disadvantages of missing users and permissions can be reduced.

    Reply
    1. hartmut

      libmysqld is still part of server distribution, but it is not seeing much love AFAICT.

      Technically SQLite has conquered much of its niche as libmysqld is much more heavy weight:

      * much bigger library
      * by linking it in your application automatically becomes multi threaded
      * a database is not just a single file but a full mysqld style data directory

      Even MySQL Workbench uses SQLite for internal storage these days …

      License issues also affect adoption of libmysqld, as it is GPL licensed like the server itself it can only get linked into GPL-compatible applications, or into inhouse applications you don’t plan to distribute (unless you buy into a license agreement with Oracle)

      And last not least it got affected by “newspeak” when “Embedded MySQL” started to refer to “MySQL bundled with your product” as in “install regular mysqld along with it” and no longer to “Embedded Library” anymore at some point …

      Reply
  2. Edmar

    Regarding performance, maybe you should also mention that SQLite concurrency is way more limited. SQLite does concurrent reads, but not concurrent read/writes, and only 1 write-at-a-time. http://www.sqlite.org/lockingv3.html

    I use SQLite mainly as a local application-level cache, and my experience with it is great (no issues to date). Enterprise databases are MySQL.

    Reply
  3. Pingback: SQLite vs MySQL. Differences explained | MYSQL | Scoop.it

Leave a Reply