fbpx
Choosing SQLite as a database (3/3)

It is the last post from the short series describing some aspects of the decision of choosing proper database engine, with a particular focus on SQLite. In this post, we see how efficient SQLite really is along with some interesting methods to tweet it. YOU CAN FIND PREVIOUS POSTS HERE AND HERE.

Accessing data

Our tests show that SQLite is much slower than MariaDB when inserting or updating data, but it is really fast when it comes to reading or aggregating large amounts of data:


On the chart median time to select large amount of rows from a database table is shown. Overall, 5 tables have been created, each having 10 times more rows than the previous one. Each table has an index with uniformly distributed 100 values. Different data series indicate different number of percent of rows that we have selected, and from which database.

Presented results are quite surprising. When selecting small amount of data, MariaDB is considerably faster. But when selecting tens of thousands or hundreds of thousands of rows, it is SQLite that is faster. Especially when no index is used. It contradicts intuitive assumption that says that if database is faster at inserting, then it should be faster at selecting. From the example it is clear that accessing data is a field where SQLite is really strong. Higher select times for lower amounts of data don’t hurt that much – when the database is small, higher  speed of single operation is not so crucial.

Also, it is worth noting that there is only a very small difference (<1%) in select times between WAL modes and normal mode.

SQLite and Indices

Indices are generally used to make queries faster. Instead of browsing through the whole table looking for rows matching the WHERE clause, proper index can point directly to the row being selected, especially when querying for one or just a couple of rows. On the other hand, there is a general rule that indices shouldn’t be used when querying for a large amount of data. How large is a “large amount” differs between databases. From our tests it looks that the break-even point (that is, when query using index is no longer faster than without index) for MariaDB is around 10% of rows – for a case when queried rows are uniformly distributed in the table. For SQLite it varies with table size and amount of data to select, but for a large groups of data this break-even point is much lower – between 4% and 5% of rows. From the other side, queries for a lots of data are generally much faster in SQLite than in MariaDB, and even queries not using any index in SQLite are generally faster than indexed queries in MariaDB. It leads us to a conclusion that SQLite is optimised for whole table searches, and that indices play much lower role than in other databases. Whether to use index is always a consideration. In our case we decided to use an index on our statistical data, because in most cases our app will benefit from it.

Subset of SQL

Another consideration to make when selecting SQLite over other database engines is that SQLite does not implement full SQL language. Overall, most queries are the same as in MariaDB, especially SELECTs. But it lacks most ALTER TABLE commands, indexes are treated differently than in other Dbs, some JOINS are missing and there are no user permissions etc. It also has a weak typing (contrary to other database engines) – when inserting a string into integer column, SQLite will firstly try to convert string to an int, but if it fails – it will insert a string. There are more quirks like that, and it is good to be aware of these quirks when designing SQLite database.

Our tests and SQL language comparison

All our tests were done in the same manner on SQLite and MariaDB. Tests have been prepared specifically to measure SQLite’s “worthiness” for our app, but they show overall trends.

Base tables were created similarly:

SQLite:

CREATE TABLE `test1_100` ( `Field1` INTEGER PRIMARY KEY AUTOINCREMENT, `Field2` TEXT, `Field3` INTEGER, `Field4` REAL, `Field5` REAL, `Field6` REAL, `Field7` REAL );

CREATE INDEX Field3 ON test1_100(Field3);

(notice how in SQLite some indexes have to be created outside of CREATE TABLE statement).

MariaDB:

CREATE TABLE `test1_100` ( `Field1` INT AUTO_INCREMENT, `Field2` TEXT, `Field3` INT, `Field4` DOUBLE, `Field5` DOUBLE, `Field6` DOUBLE, `Field7` DOUBLE, PRIMARY KEY (`Field1`), INDEX(`Field3`) );

Tables then were populated with 100 empty rows

SQLite & MariaDB: 100 times this query:

INSERT INTO test1_100 VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

Then rows were populated with random data, with exception of Field3, which has 100 consecutive values.

SQLite:

UPDATE test1_100 SET Field2=”some text”, Field3=Field1, Field4=ABS(RANDOM()), Field5=ABS(RANDOM()), Field6=ABS(RANDOM()), Field7=ABS(RANDOM());

MariaDB:

UPDATE test1_100 SET Field2=”some text”, Field3=Field1, Field4=RAND(), Field5=RAND(), Field6=RAND(), Field7=RAND();

Each table of test1 series (test1_1000, test1_10000, etc.) was created as 10 copies of data from previous table. There was also test2 series of tables, without index on Field3, created as a copy of test1 series of tables. Test1 tables were used in tests described as “using index” and test2 tables were used in tests described as “not using index”.

Tests:

Selecting % of rows:

In this test we have selected a set % of all rows in table, uniformly distributed.

SQLite & MariaDB – query for 1%:

SELECT * FROM test1_100 WHERE Field3<=1;

Selecting count of rows:

In this test we have tested how fast can DB check how many rows will be in result.

SQLite & MariaDB – query for 1%:

SELECT COUNT(Field3) FROM test1_100 WHERE Field3<=1;

Aggregating % of rows

In this test we have measured how fast database will aggregate data, using common aggregate functions such as sum, mean, min and max.

SQLite & MariaDB – query for 1%:

SELECT Field3, MAX(Field4),AVG(Field5),MIN(Field6),SUM(Field7) FROM test1_100 WHERE Field3<=1 GROUP BY Field3;

Raw tests’ results

Raw results are here:

populating table with 10 copies of previous table1001000100001000001000000
SQLite standard, second index10231116138319505769
SQLite standard, no second index10071388114114244017
SQLite WAL synchronous=FULL, second index33635239311276759
SQLite WAL synchronous=FULL, no second index3343423269094628
SQLite WAL synchronous=NORMAL, second index1220161115086
SQLite WAL synchronous=NORMAL, no second index1110114615165
SQLite WAL synchronous=OFF, second index13212202453
SQLite WAL synchronous=OFF, no second index11111031110
MariaDB, InnoDB, second index2262297147113581
MariaDB, InnoDB, no second index32522188927278
selecting single row with primary index1001000100001000001000000
SQLite standard, second index11111
SQLite standard, no second index11111
SQLite WAL synchronous=FULL, second index11111
SQLite WAL synchronous=FULL, no second index11111
SQLite WAL synchronous=NORMAL, second index11111
SQLite WAL synchronous=NORMAL, no second index11111
SQLite WAL synchronous=OFF, second index11111
SQLite WAL synchronous=OFF, no second index11111
MariaDB, InnoDB, second index11111
MariaDB, InnoDB, no second index11111
updating single row1001000100001000001000000
SQLite standard, second index1069710297108
SQLite standard, no second index12897112109119
SQLite WAL synchronous=FULL, second index2928433172
SQLite WAL synchronous=FULL, no second index2933213173
SQLite WAL synchronous=NORMAL, second index111136
SQLite WAL synchronous=NORMAL, no second index111136
SQLite WAL synchronous=OFF, second index11111
SQLite WAL synchronous=OFF, no second index11111
MariaDB, InnoDB, second index43422
MariaDB, InnoDB, no second index33223
updating 10% of rows1001000100001000001000000
SQLite standard, second index1091171929697315
SQLite standard, no second index1091181599614840
SQLite WAL synchronous=FULL, second index3334507595103
SQLite WAL synchronous=FULL, no second index3434597422398
SQLite WAL synchronous=NORMAL, second index11210486893
SQLite WAL synchronous=NORMAL, no second index1128704611
SQLite WAL synchronous=OFF, second index11123536
SQLite WAL synchronous=OFF, no second index11233512
MariaDB, InnoDB, second index35211101748
MariaDB, InnoDB, no second index37261491469
inserting single row1001000100001000001000000
SQLite standard, second index109109109107103
SQLite standard, no second index107108100107112
SQLite WAL synchronous=FULL, second index3433346767
SQLite WAL synchronous=FULL, no second index4241335067
SQLite WAL synchronous=NORMAL, second index1113325
SQLite WAL synchronous=NORMAL, no second index1112526
SQLite WAL synchronous=OFF, second index11111
SQLite WAL synchronous=OFF, no second index11111
MariaDB, InnoDB, second index23223
MariaDB, InnoDB, no second index22233
inserting 10% of rows from other table1001000100001000001000000
SQLite standard, second index109109134135117
SQLite standard, no second index117109109109109
SQLite WAL synchronous=FULL, second index3334334259
SQLite WAL synchronous=FULL, no second index3342332533
SQLite WAL synchronous=NORMAL, second index11111
SQLite WAL synchronous=NORMAL, no second index11111
SQLite WAL synchronous=OFF, second index11111
SQLite WAL synchronous=OFF, no second index11111
MariaDB, InnoDB, second index32234
MariaDB, InnoDB, no second index22234

AND HERE

Let us know, what do you think or share with us your knowledge: