↓ Skip to main content
Alessandro Ferrini

SQLite Indexes Explained: EXPLAIN QUERY PLAN, composite indexes, and when not to index

·1179 words·6 mins
Author
Alessandro Ferrini

If a query is slow, an index may help, but only when it matches the way the query filters, joins, or sorts data.

This guide focuses on SQLite indexes because SQLite is a great engine for learning how planners work. The same ideas also transfer well to PostgreSQL, MySQL, and other relational databases.

Before reading this page, it helps to understand Relational Data Modeling, Relational Normalization, and the execution layers described in Understanding DBMS Architecture.

What is an index in SQL?
#

An index is a separate data structure that helps the DBMS find rows faster than a full table scan.

Without an index, the engine may need to inspect many rows one by one. With an index, it can jump to a smaller subset first.

In SQLite, many indexes are implemented with B-trees, which keep keys ordered so lookups remain efficient.

Why indexes improve performance
#

Indexes are useful when queries frequently use:

  • WHERE
  • JOIN
  • ORDER BY
  • sometimes GROUP BY

A good index reduces the amount of data the engine must inspect.

Example without an index
#

SELECT *
FROM candidate
WHERE skill = 'Python';

If candidate.skill is not indexed, SQLite may scan the whole table.

Add the index
#

CREATE INDEX candidate_skill_idx ON candidate(skill);

Now SQLite can search the index first and then fetch matching rows.

The cost of an index
#

Indexes are not free.

Every extra index increases:

  • storage usage;
  • insert cost;
  • update cost;
  • delete cost.

That is why “index every column” is usually a bad strategy.

How to create an index in SQLite
#

CREATE INDEX index_name ON table_name(column_name);

Example:

CREATE INDEX users_email_idx ON users(email);

For uniqueness rules, use UNIQUE:

CREATE UNIQUE INDEX users_email_unique_idx ON users(email);

How to read EXPLAIN QUERY PLAN
#

The easiest way to learn indexing is to compare the same query before and after adding an index.

EXPLAIN QUERY PLAN
SELECT *
FROM candidate
WHERE skill = 'Python';

Typical planner messages include:

  • SCAN candidate
  • SEARCH candidate USING INDEX ...
  • USING COVERING INDEX ...

SCAN
#

A scan means SQLite is reading the table broadly, often row by row.

SEARCH USING INDEX
#

A search means SQLite is using an index to narrow the work.

USING COVERING INDEX
#

A covering index means SQLite can answer the query from the index alone, without going back to the table rows.

Example:

EXPLAIN QUERY PLAN
SELECT skill
FROM candidate
WHERE skill = 'Python';

If the index already contains the needed column, the planner may use a covering index.

Single-column index example
#

Suppose we have this table:

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  country TEXT NOT NULL,
  created_at TEXT NOT NULL
);

If you often search by email:

SELECT *
FROM users
WHERE email = 'alice@example.com';

this index is a strong candidate:

CREATE INDEX users_email_idx ON users(email);

Composite indexes explained
#

A composite index contains more than one column.

CREATE INDEX idx_users_name_birthday
ON users(first_name, last_name, birthday);

This index is sorted first by first_name, then last_name, then birthday.

Composite indexes are powerful, but only when their column order matches your real queries.

The left-to-right rule
#

SQLite uses composite indexes from left to right.

Given:

CREATE INDEX idx_users_name_birthday
ON users(first_name, last_name, birthday);

this query aligns well with the index:

SELECT *
FROM users
WHERE first_name = 'Ryann'
  AND last_name = 'Koch'
  AND birthday = '1986-03-11';

The planner can use all three columns.

No skipping columns
#

If you skip a middle column, later columns usually stop helping.

With the same index:

SELECT *
FROM users
WHERE first_name = 'Ryann'
  AND birthday = '1986-03-11';

SQLite can use the first_name part, but not the full composite path efficiently because last_name is missing.

Stop at the first range condition
#

Range predicates usually stop full use of the remaining columns.

SELECT *
FROM users
WHERE first_name = 'Ryann'
  AND last_name > 'Koch'
  AND birthday = '1986-03-11';

Here the index can help up to last_name, but after the range condition (>), birthday is much less useful in the same composite search.

Choosing the right column order
#

As a rule of thumb:

  • put frequently filtered equality columns first;
  • place highly selective columns early;
  • think about your actual query patterns, not theoretical ones;
  • create indexes for workload, not for schema beauty.

A good index often comes from observing repeated queries in production or during exercises.

When not to add an index
#

Do not add an index just because a column exists.

Be careful with:

  • very small tables;
  • columns rarely used in filters;
  • low-cardinality columns used alone, such as boolean flags;
  • write-heavy tables with many updates.

For example, indexing is_active alone may not help much if almost every row has the same value.

Measuring selectivity
#

Two useful questions are:

  • how many distinct values does this column have?
  • how many rows usually match the filter?

You can inspect distinctness like this:

SELECT count(DISTINCT country) * 1.0 / count(*)
FROM users;

Low selectivity often means a weaker standalone index candidate.

Indexes for joins
#

Indexes also matter for joins.

SELECT o.order_id, c.full_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'IT';

Good candidates here may include:

  • customers(customer_id) if not already the primary key;
  • orders(customer_id) for the join;
  • possibly customers(country) if the filter is frequent and selective.

Common indexing mistakes
#

Creating duplicate indexes
#

If you already have an index on (first_name, last_name), adding another one on (first_name) may be unnecessary depending on the workload.

Ignoring the query plan
#

Never assume the planner is using your index. Check with EXPLAIN QUERY PLAN.

Indexing everything after one slow query
#

Fix the workload pattern, not just the symptom.

Forgetting write cost
#

Every insert, update, and delete must also maintain index entries.

Practical workflow
#

  1. Start with a clean schema.
  2. Identify the slow query.
  3. Run EXPLAIN QUERY PLAN.
  4. Add the smallest useful index.
  5. Re-run the plan.
  6. Keep the index only if it improves the real workload.

Practice these ideas with Seagull
#

If you want a lightweight place to practice SQL before moving to a full local setup, take a look at Seagull.

It is a browser-only SQL practice project built for database courses: open an exercise, run queries, inspect results, and iterate quickly. It is especially useful for students who are still learning SELECT, filtering, joins, and query behavior.

FAQ
#

What does an index do in SQL?
#

It gives the DBMS a faster path to find rows for specific query patterns.

What is a composite index?
#

It is an index built on multiple columns, used when queries filter or sort by those columns together.

What is a covering index?
#

It is an index that contains all the data a query needs, so the engine can answer from the index alone.

Why can an index make performance worse?
#

Because indexes speed reads but slow writes and consume storage.

Next step
#

If this page made sense, go back to Understanding DBMS Architecture for the bigger execution picture, or revisit Relational Normalization to make sure you are tuning a schema that is already structurally sound.