SQL indexes: help the database find rows

Who this is for: learners familiar with tables, filters, and joins. It helps to review SQL SELECT and WHERE, joins, and transactions.

What you will learn: what an index stores, how to create and remove one, how to ask SQLite to show a query plan, and why adding indexes has costs.

The commands and query-plan example below were checked with SQLite. The index concept is common across relational databases, but syntax, planner output, and index choices vary. A query plan is not a guarantee of faster execution, especially on a tiny sample dataset.

Start with the shared sample data

These are the same customers and orders from the earlier SQL lessons. The orders.customer_id column is used to find orders for a customer and to match orders during joins.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(8, 2) NOT NULL
);

INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'Asha'),
    (2, 'Ben'),
    (3, 'Chloe');

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (101, 1, 120.00),
    (102, 1, 80.00),
    (103, 3, 200.00);

What is an index?

An index is an additional data structure the database can use to locate rows by indexed column values. You can think of it like a book index: it can help find relevant entries without examining every page, but the database decides whether using it is worthwhile for a particular query.

For example, find a customer's orders by the matching ID:

SELECT order_id, amount
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

An index on customer_id may help the database locate matching orders as the table grows.

Create an index for a common lookup

Create a named index on the orders' customer ID:

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

The index does not change the query's result. It gives the query planner another possible way to find matching rows. Primary keys and unique constraints are commonly backed by indexes created by the database, so do not add a duplicate index without checking the schema and database behavior.

Inspect a query plan in SQLite

SQLite's EXPLAIN QUERY PLAN reports the strategy it chose for a query. Run this after creating the index, on the same database:

EXPLAIN QUERY PLAN
SELECT order_id, amount
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

The plan output is database-specific and can change with the SQLite version, table size, and data distribution. On a table with only three order rows, SQLite may choose a table scan even when the index exists; that is a reasonable choice for such a tiny table, not proof the index is broken. Use realistic data and the target database's tools to evaluate an index.

Indexes have costs

• Indexes take storage in addition to the table data.
• Inserts, updates, and deletes may need to maintain relevant indexes.
• An index is most useful when it matches real query patterns; adding one to every column is not a good default.
• A multi-column index has an order. Choose its columns based on the queries you need, and check the target database's guidance.

For example, if queries commonly filter on customer_id and then sort by order_id, an index involving both columns might be worth evaluating with realistic data. Do not assume it is beneficial without inspecting plans and measuring representative workloads.

Remove an index when it is no longer needed

SQLite and many other databases support DROP INDEX, though syntax can differ. This removes the index, not the table rows:

DROP INDEX idx_orders_customer_id;

Only drop an index after checking its use and the needs of your application; an index might support queries beyond the one you are currently studying.

Try it yourself

Create an index for looking up orders by customer ID, run the filtered query, and inspect its query plan. Then consider whether that index is likely to help a table containing only three orders.

CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT order_id, amount
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

Key idea

Indexes can help a database find rows, but they consume storage and add work when data changes. Start from real queries, inspect the target database's plans, and measure using representative data before deciding which indexes to keep.