Comparison Between MySQL and SQLite

Relational Database Management Systems (RDBMS) are based on the relational model invented by Edgar F. Codd. They store data in the form of tables, and allow the data to be linked by establishing a relationship between the tables. MySQL and SQLite are both based on the relational database management system model.

SQLite

SQLite is an embedded, file-based RDBMS. It is a server-less database, and does not require any installation or setup. Database is contained within a single disk file and all reads and writes take place directly on this disk file. SQLite adheres to the ACID properties to safeguard transactions against memory allocation failures and disk I/O errors.

MySQL

It is a database server. Therefore, it can’t be directly integrated with app like SQLite. MySQL requires installation and then app can connect to it. A application uses the TCP/IP protocol to send and receive data from the database. It is one of the most popular open-source and large-scale RDBMS systems. Multi-threaded nature of MySQL allows for greater performance.

Comparison

• SQLite reads and writes directly to disk file. Only applicable access permissions are the typical access permissions of the underlying operating system. Unlike SQLite, MySQL support user management and allows you to grant access privileges on a user-by-user basis.
• Multiple processes can access and query an SQLite database at the same time. But only one process can make changes to the database at any given time. So SQLite supports concurrency, but not as much as MySQL.
• MySQL needs a server to run and it will need a client and server architecture for interacting over a network. SQLite is a server-less database and self-contained.
• SQLite supports data types such as Blob, Integer, Null, Text, Real. MySQL supports large number of data types. For example Tinyint, Smallint, Mediumint, Int, Bigint, Double, Float, Real, Decimal, Numeric, Timestamp, Date, Datetime, Char, Varchar, Tinytext, Tinyblob, Blob, Text.
• MySQL is highly scalable and can handle a large volume of data very efficiently. SQLite can handle only small set of data if the volume of data increased its performance degrades.