SQLite Expression-based Index

Summary: in this tutorial, you will learn how to use the SQLite expression-based index to query data to improve the query performance especially for the queries that use expression or function.

Introduction to the SQLite expression-based index

When you create an index, you often use one or more columns in a table. Besides the normal indexes, SQLite allows you to form an index based on expressions involved table columns. This kind of index is called an expression based index.

The following query selects the customers whose the length of the company is greater than 10 characters.

SELECT customerid,
 company
 FROM customers
 WHERE length(company) > 10
 ORDER BY length(company) DESC;

Code language: SQL (Structured Query Language) (sql)

If you use the EXPLAIN QUERY PLAN statement, you will find that SQLite query planner had to scan the whole customers table to return the result set.

EXPLAIN QUERY PLAN
 SELECT customerid,
 company
 FROM customers
 WHERE length(company) > 10
 ORDER BY length(company) DESC;

Code language: SQL (Structured Query Language) (sql)

The SQLite query planner is a software component that determines the best algorithm or query plan to execute an SQL statement. As of SQLite version 3.8.0, the query planner component was rewritten to run faster and generate better query plans. The rewrite is known as the next generation query planner or NGQP.

To create an index based on the expression LENGTH(company), you use the following statement.

CREATE INDEX customers_length_company
 ON customers(LENGTH(company));

Code language: SQL (Structured Query Language) (sql)

Now if you execute the query above again, SQLite will use the expression index to search to select the data, which is faster.

How the SQLite expression-based index work

The SQLite query planner uses the expression-based index only when the expression, which you specified in the CREATE INDEX statement, appears the same as in the WHERE clause or ORDER BY clause.

For example, in the sample database, we have the invoice_items table.

The following statement creates an index using the unit price and quantity columns.

CREATE INDEX invoice_line_amount
 ON invoice_items(unitprice*quantity);

Code language: SQL (Structured Query Language) (sql)

However, when you run the following query:

EXPLAIN QUERY PLAN
 SELECT invoicelineid,
 invoiceid,
 unitprice*quantity
 FROM invoice_items
 WHERE quantity*unitprice > 10;

Code language: SQL (Structured Query Language) (sql)

The SQLite query planner did not use the index because the expression in the CREATE INDEX ( unitprice*quantity) is not the same as the one in the WHERE clause (quantity*unitprice)