Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 33 ยท Performance

Indexes

Speed up SQLite queries with indexes: single and composite indexes, column order, covering, partial, expression and unique indexes, and their costs.

Without help, SQLite answers WHERE customer_id = 42 by reading every row of the table and checking each one. That is fine for a few hundred rows and painful for a few million. An index is a second, sorted copy of one or more columns, with a pointer back to the full row. Like the index at the back of a book, it lets SQLite jump straight to the matching entries instead of reading every page.

Indexes are the single biggest performance tool in SQLite, and also the easiest one to misuse. This lesson shows how to create them, how column order in a multi-column index works, and which special kinds (covering, partial, expression, unique) solve which problems. To see whether SQLite actually uses an index, the next lesson, EXPLAIN QUERY PLAN and the Optimizer, is the companion. Here we peek at the plan just enough to prove each point.

What an index does

Sorted copy Chosen columns, kept in order.
Jump, don't scan Find matches in a few steps.
Costs on write Every change updates it too.
1 / 2

Creating an index

The syntax is short:

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

The tutorial examples below use an orders table filled with 2,000 generated rows, so there is something to search. A recursive query makes the rows (see Joins, Subqueries and CTEs). Run the example and read the last two result tables: the first plan says SCAN, the second says SEARCH ... USING INDEX.

SQL Before and after an index

Press Run (or Ctrl + Enter) to see the result here.

The count is 20 both before and after: an index never changes the answer, only how SQLite finds it. In the detail column of the first plan you read SCAN orders (every row is visited). In the second you read SEARCH orders USING INDEX idx_orders_customer (customer_id=?): SQLite walks the sorted index to the entries for customer 5 and fetches only those 20 rows.

The other columns of the plan output (id, parent, notused) are internal bookkeeping and their values can differ from run to run. Read the detail text.

Two details worth knowing. Indexes you create are stored in the same database file as the table and kept up to date automatically; there is nothing to refresh. And SQLite already builds some indexes for you: PRIMARY KEY and UNIQUE constraints create one, and an INTEGER PRIMARY KEY is the table's own row order, so lookups by id are always fast (SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)). See Tables, rowid and INTEGER PRIMARY KEY.

Which columns deserve an index?

Index the columns you search or join on a lot:

  • columns in frequent WHERE filters (customer_id, email, status combined with others),
  • the child column in a foreign-key relationship, because joining parent to children looks up by it (and ON DELETE checks need it too, see Foreign Keys),
  • columns you ORDER BY or GROUP BY on large result sets.

Do not bother with tiny tables, or with columns that have very few distinct values (a done flag that is 0 or 1 for half the rows each): an index that matches half the table helps very little.

Composite indexes and column order

An index can cover several columns. The order is the most important design decision, because the index is sorted by the first column, then by the second inside each first-column value, and so on, like a phone book sorted by last name and then first name.

That means an index on (customer_id, status, created) can answer a search that fixes the leftmost columns: customer_id alone, customer_id and status, or all three. It cannot jump to a status value if the query does not also fix customer_id.

SQL Leftmost-prefix rule

Press Run (or Ctrl + Enter) to see the result here.

The first, second and fourth queries each read SEARCH orders USING INDEX idx_cust_status_created (...) with the columns they could use listed in parentheses. The third, which skips customer_id, falls back to SCAN orders.

Rules of thumb for ordering the columns:

  1. Put columns compared with = first.
  2. Put the column with a range (>, <, BETWEEN) or the ORDER BY column after them. Once the index hits a range column, columns after it can no longer narrow the search.
  3. Among equality columns, order barely matters for lookups, so choose the order that also serves other queries you run.

A composite index also makes a separate index on its first column redundant: (customer_id, status, created) already serves every search on customer_id alone. Dropping the extra one saves space and write time:

SQL A redundant index

Press Run (or Ctrl + Enter) to see the result here.

After the drop, only idx_b is left and the plan for a customer_id lookup still reads SEARCH orders USING INDEX idx_b (customer_id=?). The helper pragma_index_list('table') is a table-valued function that lists a table's indexes inside an ordinary SELECT.

Sorting without sorting

An index is already in order, so it can also remove the sort step. When customer_id is fixed and the next index column is created, rows come out of the index already ordered by created:

SQL Index that serves ORDER BY

Press Run (or Ctrl + Enter) to see the result here.

With only the one-column index, the plan has an extra line USE TEMP B-TREE FOR ORDER BY: SQLite found the rows by index and then sorted them. With the composite index that line disappears and LIMIT queries can stop early.

Covering indexes

When all the columns a query needs are inside the index, SQLite never visits the table at all. That is a covering index, and the plan says USING COVERING INDEX. You get it by choosing the index columns to match a hot query, or by appending a couple of extra columns at the end.

SQL A covering index

Press Run (or Ctrl + Enter) to see the result here.

The first plan reads SEARCH orders USING COVERING INDEX idx_customer_total (customer_id=?); the second drops the word COVERING. Rows in the table are often much wider than the index entries, so covering queries touch far less data. Note that rowid (or an INTEGER PRIMARY KEY) is always available inside every index for free.

Partial and expression indexes

A partial index has a WHERE clause and contains only the rows that satisfy it. It is smaller and cheaper to maintain, and perfect when you mostly query a small, "active" slice, such as open orders or unprocessed jobs. SQLite uses it only when the query's own WHERE implies the index's condition.

An expression index stores the result of an expression, so a filter on that expression can use it. Wrapping a column in a function normally defeats a plain index: lower(email) = 'x' cannot use an index on email. Index the same expression and it can.

SQL Partial and expression indexes

Press Run (or Ctrl + Enter) to see the result here.

The first plan reads SEARCH orders USING COVERING INDEX idx_open_created (created=?) (it is even covering: id is always in the index and status is known to be 'open' for every entry). The second falls back to SCAN orders, and the third reads SEARCH users USING INDEX idx_email_lower (<expr>=?). For a case-insensitive email column there is an even simpler alternative: declare the column COLLATE NOCASE and the plain index will already compare without case.

Unique indexes

CREATE UNIQUE INDEX is the same as a UNIQUE constraint, but you can add it after the table exists and even make it partial or expression-based. That makes it handy for rules such as "only one active subscription per user".

SQL UNIQUE and partial UNIQUE

Press Run (or Ctrl + Enter) to see the result here.

Two inactive rows and one active row for user 1 are accepted. The last insert fails with UNIQUE constraint failed: subscriptions.user_id. Remember from Constraints and STRICT Tables that several NULLs never conflict in a unique index.

When indexes hurt

An index is not free:

  • Writes get slower. Every INSERT adds an entry to every index on the table, and an UPDATE of an indexed column rewrites its entries. A table with eight indexes does nine writes per insert.
  • The file grows. Each index stores its columns again.
  • Planner confusion. With many overlapping indexes SQLite has more choices to weigh and sometimes picks a poorer one.
  • Low-selectivity indexes (few distinct values) rarely pay for themselves.

A practical routine: add an index when a real, frequent query needs it, confirm with EXPLAIN QUERY PLAN that it is used, and drop indexes nothing uses. DROP INDEX name is instant, and PRAGMA index_list('table') and sqlite_master show what exists. When you load a large batch of data, a common speed-up is to create the indexes after the bulk insert instead of before.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table has an index on (a, b, c). Which query can use the index to jump to matching rows?

    An index is sorted by its first column, then the second inside that, and so on. A search must fix the leftmost columns, so a and b together work, while queries that skip a cannot use it to jump.
  2. 2. What does USING COVERING INDEX in a query plan tell you?

    A covering index answers the query on its own, which avoids fetching the wider table rows.
  3. 3. Why does WHERE lower(email) = 'x' not use a plain index on email?

    The index is sorted by the stored column. Create an expression index on lower(email) (or use COLLATE NOCASE) to support that filter.
  4. 4. What is a partial index?

    A partial index is smaller and cheaper to maintain, and SQLite uses it when the query's WHERE clause implies the index's condition.
  5. 5. Which is a real cost of adding many indexes to a table?

    Indexes speed up reads at the expense of write time and file size, so add only the ones real queries need.

Summary

  • An index is a sorted copy of chosen columns that lets SQLite search instead of scanning; it never changes results, only speed.
  • In a composite index, column order is everything: equality columns first, then a range or ORDER BY column, and searches must fix the leftmost columns.
  • A composite index makes an index on just its first column redundant, and can also remove the USE TEMP B-TREE FOR ORDER BY sort step.
  • Covering indexes contain every needed column; partial indexes hold only a slice of rows; expression indexes support filters on computed values; unique indexes enforce rules.
  • Indexes cost write time and space: add them for real queries, check with EXPLAIN QUERY PLAN, and drop the unused.

Next: EXPLAIN QUERY PLAN and the Optimizer shows how to read what SQLite decides, and how ANALYZE helps it decide well.