SQL Indexing Best Practices

An index is a separate, sorted data structure that the database engine maintains alongside a table so it can find rows without scanning every single one. Think of it like the index at the back of a textbook: instead of reading every page to find “normalization,” you jump straight to the page number listed under N. Indexes are the single biggest lever you have over query performance, but they are not free — used well they turn slow scans into instant lookups, and used carelessly they bloat storage and slow down writes.

Overview: How Indexes Work

Without an index, the database engine answers a filtered query with a full table scan: it reads every row, checks the condition, and keeps the matches. For a table with a few thousand rows this might be fast enough. For a table with millions of rows, it means reading millions of rows just to return ten.

Most relational databases, including SQLite, implement indexes as B-tree (balanced tree) structures. A B-tree keeps indexed values in sorted order across a tree of pages, so looking up a value takes roughly O(log n) comparisons instead of O(n). Each leaf entry in the index stores the indexed column value(s) plus a pointer back to the actual row (in SQLite, the table’s rowid; in other engines, a row identifier or the primary key). To fetch the full row, the engine does an extra lookup from the index entry back into the table — unless every column the query needs is already inside the index itself, which is called a covering index (more on that below).

Creating an index does not change what a query returns — it only changes how fast the engine can find the answer. The query planner (SQLite’s included) decides at run time whether using an index is actually cheaper than a full scan, based on statistics about how selective a condition is. A primary key column is automatically indexed in SQLite when declared INTEGER PRIMARY KEY (it becomes an alias for the rowid), but every other column you filter, join, or sort on needs an index you create explicitly.

Syntax

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
  ON table_name (column1 [ASC|DESC], column2, ...)
  [WHERE condition];

DROP INDEX index_name;

Part	Meaning
UNIQUE	Optional. Also enforces that no two rows share the same value(s) in the indexed column(s) — an error is raised on insert/update if violated.
IF NOT EXISTS	Avoids an error if an index with that name already exists.
index_name	A name you choose. A common convention is idx_<table>_<columns>.
table_name (columns...)	The table and the column(s), in order, that make up the index. Order matters for multi-column (composite) indexes — see below.
WHERE condition	Optional. Creates a partial index covering only rows matching the condition — smaller and faster for narrow queries.

Dropping an index removes the auxiliary structure but never touches the underlying data:

CREATE INDEX idx_employees_department_temp ON employees(department);
DROP INDEX idx_employees_department_temp;

Examples

Example 1: A single-column index

CREATE INDEX idx_employees_department ON employees(department);
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department = 'Engineering';

Result (EXPLAIN QUERY PLAN):

id	parent	notused	detail
0	0	0	SEARCH employees USING INDEX idx_employees_department (department=?)

Instead of scanning all 4 rows of employees, the planner uses the new index to jump straight to the rows where department = 'Engineering'. The word SEARCH (instead of SCAN) confirms the index is being used.

Example 2: A composite (multi-column) index

CREATE INDEX idx_employees_dept_salary ON employees(department, salary);
EXPLAIN QUERY PLAN
SELECT name, salary FROM employees
WHERE department = 'Sales' AND salary > 50000;

Result: one row — SEARCH employees USING INDEX idx_employees_dept_salary (department=? AND salary>?).

A composite index on (department, salary) is sorted first by department, then by salary within each department. That lets SQLite satisfy both conditions using one index lookup. Column order is critical here: this index can efficiently filter on department alone, or on department + salary together, but it is far less useful for a query that filters on salary alone, because salary values aren’t grouped together across the whole index — only within each department.

Example 3: Indexing a foreign-key-style column (full example)

CREATE TABLE order_history (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO order_history (order_id, customer_id, order_date, amount) VALUES
  (1, 101, '2026-01-05', 250.00),
  (2, 102, '2026-01-06', 99.50),
  (3, 101, '2026-02-10', 430.00),
  (4, 103, '2026-02-15', 75.00),
  (5, 102, '2026-03-01', 610.00);

CREATE INDEX idx_order_history_customer_date ON order_history(customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT * FROM order_history
WHERE customer_id = 101 AND order_date >= '2026-02-01';

Result: one row — SEARCH order_history USING INDEX idx_order_history_customer_date (customer_id=? AND order_date>?), matching only order_id 3 (customer 101, dated 2026-02-10).

Foreign-key-like columns (a customer_id used to join back to a customers table) are among the most valuable columns to index, because joins constantly filter and match on them. Without this index, joining order_history to customers for a single customer would require scanning every order row.

How It Works Step by Step / Under the Hood

When SQLite plans a query, it walks through the logical processing order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — and for each table involved in FROM/WHERE, it asks: “is there an index whose leading column(s) match a condition in this query, and would using it be cheaper than scanning the whole table?” It answers that using stored statistics (gathered by running ANALYZE) about how many distinct values a column has and how rows are distributed.

If the query only needs columns that are already present in the index itself, SQLite can skip visiting the table entirely — this is a covering index, and it shows up as USING COVERING INDEX in the query plan:

CREATE INDEX idx_employees_dept_salary2 ON employees(department, salary);
EXPLAIN QUERY PLAN
SELECT department, salary FROM employees WHERE department = 'Engineering';

Result: SEARCH employees USING COVERING INDEX idx_employees_dept_salary2 (department=?). Because both department and salary live inside the index, SQLite never has to jump back to the actual table row — a covering index is one of the fastest possible ways to answer a query.

Indexes also come into play for ORDER BY and GROUP BY: if an index already stores rows in the required sort order, the engine can skip a separate, expensive sort step. This is why an index designed for filtering often speeds up sorting too, as long as the sort columns come next in the index definition.

Common Mistakes

Mistake 1: Creating a UNIQUE index on a column that isn’t actually unique

CREATE UNIQUE INDEX idx_employees_department_unique ON employees(department);

This fails immediately, because more than one employee shares the same department value (only 3 departments across 4 employees). UNIQUE is a constraint, not just a performance hint — SQLite rejects the index creation (or later inserts) the moment duplicate values are found.

Corrected: use a plain index when you just want faster lookups, and reserve UNIQUE for columns that are genuinely one-per-row, like an email address or an order number:

CREATE INDEX idx_employees_department_plain ON employees(department);

Mistake 2: Wrapping the indexed column in a function

CREATE INDEX idx_employees_hire_date_a ON employees(hire_date);
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE strftime('%Y', hire_date) = '2023';

Result: SCAN employees — the index is completely ignored. This query runs and returns correct rows, but it’s slow on a large table. SQLite indexes the raw stored value of hire_date, not the result of strftime() applied to it, so it cannot use the index to jump to matching rows; it must compute the function for every row first.

Corrected: rewrite the condition as a plain range comparison on the raw column so the index applies:

CREATE INDEX idx_employees_hire_date_b ON employees(hire_date);
EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

Result: SEARCH employees USING INDEX idx_employees_hire_date_b (hire_date>? AND hire_date<?) — same rows, index now used. (Some databases support function-based/expression indexes that solve this differently; SQLite supports expression indexes too, but the range rewrite above is simpler and more portable.)

Mistake 3: Indexing everything “just in case”

Every index speeds up reads but slows down writes: each INSERT, UPDATE, or DELETE has to update every index on that table, not just the table itself. A table with ten rarely-used indexes can make simple inserts noticeably slower and bloats disk usage, while most of those indexes never get chosen by the query planner. Index the columns your actual queries filter, join, or sort on — not every column that might someday be useful.

Best Practices

• Index columns used in WHERE, JOIN ... ON, and ORDER BY clauses — especially foreign-key columns.
• For composite indexes, put the column used in equality filters (=) first, and range/sort columns (>, <, ORDER BY) after.
• Use EXPLAIN QUERY PLAN before and after adding an index to confirm it’s actually chosen — don’t guess.
• Avoid indexing low-cardinality columns alone (like a boolean flag with only two values) — the planner often decides a scan is cheaper anyway.
• Never wrap an indexed column in a function or arithmetic expression inside a WHERE clause unless you’ve created a matching expression index.
• Periodically run ANALYZE so the query planner’s statistics stay accurate as data grows and changes.
• Reserve UNIQUE indexes for columns that must be one-per-row; use them to enforce data integrity, not just speed.
• Drop indexes that EXPLAIN QUERY PLAN shows are never used — they only cost you on every write.

Practice Exercises

• Exercise 1: Using the customers table, create an index that would speed up a query filtering by country. Then write the EXPLAIN QUERY PLAN statement you’d use to confirm it’s being used.
• Exercise 2: The orders table has both customer_id and order_date. Design a composite index to speed up a query that finds all orders for a specific customer placed after a given date. Which column should come first, and why?
• Exercise 3: Explain why CREATE UNIQUE INDEX idx_products_category ON products(category); would likely fail against the sample products table, and describe what column (or column combination) would be a safer choice for a unique index.

Summary

• An index is a sorted B-tree structure that lets the engine find rows in roughly O(log n) time instead of scanning every row.
• Creating an index never changes query results — only how fast the engine can produce them.
• Composite index column order matters: put equality-filtered columns first, range/sort columns after.
• A covering index (containing every column a query needs) lets the engine skip visiting the table entirely.
• Wrapping an indexed column in a function usually disables the index silently — rewrite as a raw-column comparison when possible.
• UNIQUE indexes enforce data integrity, not just speed, and fail if duplicate values exist.
• Every index adds overhead to writes, so index deliberately based on real query patterns, not preemptively.
• Use EXPLAIN QUERY PLAN to verify, never assume, that an index is actually being used.