↓ Skip to main content
Alessandro Ferrini

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

·1352 words·7 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 when indexed values change;
  • 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 indicates a full scan of a table or an index, including a covering index. It does not by itself mean that indexes are unused or that the plan is inefficient.

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,
  first_name TEXT,
  last_name TEXT,
  birthday TEXT,
  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 normally does not narrow the search bounds further.

The no-skipping and first-range rules describe the bounds of a typical index search. Later columns can still help filter results or cover the query, and SQLite can use skip-scan in specific circumstances. Check the actual plan rather than treating these rules as absolute.

Choosing the right column order
#

As a rule of thumb:

  • put frequently filtered equality columns first;
  • consider selective columns early only when that order matches the queries and index prefixes you actually use;
  • 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 measure overall diversity as the ratio of distinct values to rows:

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

This ratio does not tell you how many rows match country = 'IT'. Inspect the distribution too:

SELECT country, count(*) AS row_count
FROM users
GROUP BY country
ORDER BY row_count DESC;

A filter matching a large fraction of rows is often a weaker standalone index candidate. A column with few distinct values can still have a rare value worth indexing.

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
#

Inserts and deletes maintain index entries; updates need to maintain an index when they change values it indexes.

Practical workflow
#

  1. Start with a clean schema.
  2. Identify the slow query.
  3. Run EXPLAIN QUERY PLAN and record the query’s execution time.
  4. Add the smallest useful index.
  5. Re-run the plan and time the query again with comparable data, cache state, and load.
  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?
#

Indexes can speed up matching read queries, but maintaining them adds write costs and consumes 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.