Indexing

An index can make your SQL queries faster and cheaper. It is a data structure that improves the speed of data retrieval operations on a given table. Indexes are like a table of contents in a book, where you can quickly find the page number of a chapter. As your database grows your queries will have to scan more rows to find the data you are looking for. Indexes help to reduce the number of rows that need to be scanned.

Creating an index

Not all columns in a table need to be indexed. You should only index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses. Indexes can be created on single or multiple columns.

To create an index, you need to use the CREATE INDEX statement.

Let's use this table as an example:

To create an index on the email column in the users table:

A good naming format for indexes is idx_tablename_columnnames to make it easier to manage and identify the indexes.

Querying that now uses email will benefit from the index:

​​Multi-column indexes

For a multi-column index (an index that specifies multiple columns), queries will only use the index if they specify either all of the columns, or a subset of the columns provided all columns to the “left” are also within the query.

Given the below index, the table shows when the index is used (or not):

Query	Index Used?
SELECT * FROM transactions WHERE customer_id = '1234' AND transaction_date = '2023-03-25'	Yes: specifies both columns in the index.
SELECT * FROM transactions WHERE transaction_date = '2023-03-28'	No: only specifies transaction_date, and does not include other leftmost columns from the index.
SELECT * FROM transactions WHERE customer_id = '1234'	Yes: specifies the leftmost column in the index.

Partial indexes

Partial indexes are indexes over a subset of rows in a table. Partial indexes are defined by the use of a WHERE clause when creating the index. A partial index can be useful to omit certain rows, such as those where values are NULL or where rows with a specific value are present across queries.

Test index

Validate that an index was used for a query by prepending a query with EXPLAIN QUERY PLAN. This will output a query plan for the succeeding statement, including which (if any) indexes were used.

If the index is used, you will see USING INDEX <INDEX_NAME> as part of the query plan output.

Listing indexes

Query the sqlite_schema system table to list all indexes in the database.

Dropping an index

To drop an index, you need to use the DROP INDEX statement.

Dashboard

You can also create and manage indexes from the dashboard.