Performance Tuning

This page is an area where you can place code, hints, suggestion or text that relates to increasing the speed of the SQLite databases.

Place indexes on join and where column

Placing indexes on columns that are used in joins or where statements are the best way to increase query speed.

Use cluster indexes

Clustered indexes are indexes that comprise more than one column. These increase the speed of queries where you have multiple joins of one table or you are selecting rows based on multiple columns. (This is true in other DB systems, I don't know if it holds true in SQLite.)

How SQLite works out JOINs

From an email by D. Richard Hipp on 13 January 2004:

SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.

So if there work in working out joins it stands to reason that doing that work for SQLite will speed up the compile times. If you are returning 100's of rows this might not matter but if you are returning 1 row many times this might be a big issue. (Does anyone understand this paragraph? I don't. It should probably be rewritten or removed.)

I think the above paragraph is pointing out that SQLite translates the JOIN syntax to WHERE expression clauses before byte-coding. The suggestion is that using WHERE clauses in your SQL statements will eliminate this step and thus speed up the compilation of the SQL. This is probably true, but the savings will be quite miniscule. Perhaps around 2 or 3 malloc()/free() calls per JOIN clause. PrecompiledSql might be a better trick.

Order your tables properly in your where clause

Place the tables where you can eliminate the most rows by using a where clause (preferably on an indexed column) first, in order to limit the number of JOIN operations required.

Idexes on INTEGER PRIMARY KEY columns (don't do it)

When you create a column with INTEGER PRIMARY KEY, SQLite uses this column as the key for (index to) the table structure. This is a hidden index (as it isn't displayed in SQLite_Master table) on this column. Adding another index on the column is not needed and will never be used. In addition it will slow INSERT, DELETE and UPDATE operations down.

Use transactions when updating tables

Make sure that you wrap up all multiple updates inside a transaction, e.g.:

BEGIN TRANSACTION;
UPDATE table1 SET col1='1';
UPDATE table1 SET col1='2';
...
INSERT INTO table1(col1) VALUES ('2');
...
COMMIT TRANSACTION;

(The word "TRANSACTION" is optional.)

Using a transaction is the fastest way to update data in SQLite. Basically, this is how it works: After each transaction the SQLite engine closes and opens the database file. When SQLite opens a database file it populates the SQlite internal structures, which takes time. So if you have 100 updates and don't use a transaction then SQlite will open and close the database 100 times. Using transactions improves speed. Use them.