SQLite - Indexes

SQLite - Indexes for Performance

Indexes are special lookup tables that the database search engine uses to speed up data retrieval. An index acts as a pointer to data in a table, much like the index in the back of a book. When you need to find a specific topic, you don’t read the whole book; you look up the topic in the index, which points you to the exact page numbers.

In a database, an index helps accelerate SELECT queries, especially those with WHERE clauses. However, this speed comes at a cost: indexes slow down data modification operations like INSERT, UPDATE, and DELETE, because the index itself must also be updated. You can create or drop indexes at any time without affecting the table’s data.

Understanding When to Use an Index

The key to using indexes effectively is understanding the query patterns of your application. You can use the EXPLAIN QUERY PLAN command to see how SQLite executes a query and whether it’s using an index.

Let’s assume we have a table of users:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Let's find a user by their username
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE username = 'alice';

The output will likely be:

QUERY PLAN
`--SCAN TABLE users

SCAN TABLE means SQLite has to read every row in the table (a full table scan) to find the user. This is inefficient for large tables.

The CREATE INDEX Command

The CREATE INDEX statement allows you to build an index on one or more columns of a table. You provide a name for the index, the table it applies to, and the columns to include.

CREATE INDEX index_name ON table_name (column1, column2, ...);

Let’s create an index on the username column and re-run our query plan:

CREATE INDEX idx_users_username ON users (username);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE username = 'alice';

Now, the output will change, indicating the index is being used:

QUERY PLAN
`--SEARCH TABLE users USING INDEX idx_users_username (username=?)

SEARCH TABLE USING INDEX confirms that SQLite can now jump directly to the relevant data, which is much faster.

Types of Indexes

Single-Column Indexes

An index created on a single table column. This is the most common type of index.

CREATE INDEX idx_users_username ON users (username);

Unique Indexes

A unique index ensures that all values in the indexed column (or combination of columns) are distinct. It serves both for performance and data integrity. Trying to insert a duplicate value will result in an error.

-- Ensures no two users can have the same email address.
CREATE UNIQUE INDEX uidx_users_email ON users (email);

Composite (Multi-Column) Indexes

An index on two or more columns of a table. These are useful when you frequently filter by a combination of columns. The order of columns in the index matters.

-- For a table of products, to quickly find products in a specific category and price range.
CREATE INDEX idx_products_category_price ON products (category_id, price);

This index will be most effective for queries that filter on category_id or on both category_id and price. It is less effective for queries that only filter on price.

Advanced Indexing: Expressions and Partial Indexes

Modern SQLite versions support more advanced indexing strategies.

• Indexes on Expressions: You can create an index on an expression or function. This is useful for case-insensitive searches.
• Partial Indexes: An index that only includes a subset of a table’s rows, defined by a WHERE clause. This can save space and improve performance if you frequently query a specific subset of data.

-- Index for case-insensitive username lookups
CREATE INDEX idx_users_username_nocase ON users(LOWER(username));

-- Index only for active, high-value orders
CREATE INDEX idx_active_orders ON orders(order_date) WHERE status = 'active' AND amount > 1000;

Implicit Indexes

Implicit indexes are created automatically by SQLite when you define PRIMARY KEY or UNIQUE constraints on a table. For our users table, SQLite automatically created an index for the id column (PRIMARY KEY) and the email column (UNIQUE).

You can list all indexes in the database (including implicit ones) from the schema table:

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

The DROP INDEX Command

To remove an index, use the DROP INDEX command. Be cautious when dropping indexes, as it may degrade query performance. It’s wise to measure performance before and after dropping an index.

DROP INDEX IF EXISTS idx_users_username;

Using IF EXISTS prevents an error if the index has already been dropped.

When to Avoid Indexes

While powerful, indexes are not always the right solution. Here are some guidelines on when to reconsider adding an index:

• Small Tables: For tables with only a few hundred rows, a full table scan can be faster than the overhead of reading the index first and then fetching the data.
• Tables with Heavy Write Loads: If a table has frequent INSERT, UPDATE, or DELETE operations, the cost of maintaining each index can become significant and slow down your application.
• Columns with Low Cardinality: Indexing a column with very few unique values (e.g., a boolean ‘status’ column) is often not helpful, as the index won’t be selective enough to significantly narrow down the search.
• Columns That Are Rarely Queried: Don’t add an index for a column that is not frequently used in WHERE clauses.