SQL Indexes Explained: How an Index Speeds Up Your Queries

Open a thick accounts textbook and look for "depreciation". You could read every page from the start until you find it. Or you could turn to the index at the back, where the topics are sorted alphabetically, find "Depreciation ... 212, 247", and go straight to those two pages.

The index is not the book. It is a small, sorted list of topics with page numbers, kept separately. It costs the publisher a few extra pages, and every time a chapter is rewritten someone has to update it. In return, finding anything takes seconds.

A database table is the book, read page by page in a full table scan. A SQL index is the sorted list at the back. The page numbers are row locations, and turning straight to them is an index lookup.

A SQL index is a database object that stores the values of one or more columns in sorted order with pointers to the matching rows, so queries can find rows quickly instead of scanning the entire table.

Malwa Mart now has a year of orders, not eleven. This article measures what an index changes on 100,000 rows, shows how to read a query plan, and explains when an index helps and when it is ignored. It follows database normalization, whose joins depend on fast key lookups.

How does a SQL index work?

Most databases store indexes as a B-tree, a balanced tree of sorted pages. The top page splits the key range into chunks, the next level splits each chunk again, and the bottom level, the leaf pages, holds every value with a pointer to its row.

Without an index, the database checks every row. With one, it walks down a few levels of the sorted tree and reads only the matching rows.

Because each level narrows the search a lot, a B-tree over a million rows is usually only three or four levels deep. That is why an index lookup costs a handful of page reads while a scan costs thousands.

Some facts about indexes that hold in SQLite, MySQL and PostgreSQL:

• A primary key is indexed automatically. So is a UNIQUE constraint.
• Foreign keys are not always indexed. MySQL's InnoDB creates an index for them; PostgreSQL and SQLite do not, so you add one yourself.
• The query planner decides whether to use an index. You never name the index in a normal SELECT.

Creating an index and measuring the difference

The syntax is short. Name the index after its table and columns, so it is easy to recognise later.

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE UNIQUE INDEX idx_customers_phone ON customers (phone);
DROP INDEX idx_orders_customer;            -- MySQL: DROP INDEX idx_orders_customer ON orders;

The block below generates 100,000 orders with a recursive CTE, then runs the same query before and after creating the index. EXPLAIN QUERY PLAN shows SQLite's strategy.

import sqlite3, time

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, order_date TEXT, amount INT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 100000)
INSERT INTO orders SELECT i, 1 + (i * 7919) % 5000, 1 + i % 4,
       date('2025-01-01', '+' || (i % 365) || ' days'), 100 + (i * 37) % 1500 FROM n;
""")
query = "SELECT COUNT(*), SUM(amount) FROM orders WHERE customer_id = 42"

def run(label):
    plan = [row[3] for row in con.execute("EXPLAIN QUERY PLAN " + query)]
    start = time.perf_counter()
    for _ in range(20):
        result = con.execute(query).fetchone()
    ms = (time.perf_counter() - start) / 20 * 1000
    print(f"{label}: {result}  {ms:.3f} ms  plan: {plan}")

run("before index")
con.execute("CREATE INDEX idx_orders_customer ON orders (customer_id)")
run("after index ")

Before the index, the plan says SCAN orders: all 100,000 rows are read to find customer 42's 20 orders. After it, the plan says SEARCH orders USING INDEX, and the query typically runs a few hundred times faster. Your exact timings will differ by machine, and in the browser, but the gap will be large.

Reading query plans in other databases

PostgreSQL's EXPLAIN ANALYZE actually runs the query and reports real times, which makes it the best tool for checking a slow report.

Composite indexes and the leftmost prefix rule

An index can cover several columns. CREATE INDEX idx_store_date ON orders (store_id, order_date) sorts rows by store first and, within each store, by date, like a phone book sorted by surname and then first name.

The index is sorted by its first column. A date-only filter would have to jump around every store's section, so the planner scans the table instead.

This creates the leftmost prefix rule: the index helps a query that filters on the first column, or on the first and second columns, but not on the second column alone.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, order_date TEXT, amount INT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO orders SELECT i, 1 + i % 500, 1 + i % 4, date('2025-01-01', '+' || (i % 365) || ' days'), 100 + i % 900 FROM n;
CREATE INDEX idx_store_date ON orders (store_id, order_date);
""")
queries = {
    "store + date range": "SELECT * FROM orders WHERE store_id = 3 AND order_date >= '2025-06-01' AND order_date < '2025-07-01'",
    "store only":         "SELECT * FROM orders WHERE store_id = 3",
    "date only":          "SELECT * FROM orders WHERE order_date = '2025-06-01'",
    "covering":           "SELECT store_id, order_date FROM orders WHERE store_id = 2 AND order_date = '2025-03-03'",
    "function on column": "SELECT * FROM orders WHERE strftime('%m', order_date) = '06' AND store_id + 0 = 3",
}
for label, sql in queries.items():
    plan = [row[3] for row in con.execute("EXPLAIN QUERY PLAN " + sql)]
    print(f"{label:19} -> {plan}")

Store plus date and store alone both SEARCH the index. Date alone falls back to SCAN. The covering query needs only columns stored in the index, so SQLite answers it from the index without touching the table at all, which is the fastest case.

The last query wraps both columns in expressions. The planner cannot look up strftime('%m', order_date) or store_id + 0 in an index sorted by the raw values, so it scans. That is why the date functions article recommends order_date >= '2026-01-01' AND order_date < '2027-01-01' over strftime('%Y', order_date) = '2026'.

When should you not add an index?

Indexes are not free. Every INSERT, UPDATE and DELETE must update every index on the table, and each index takes disk space. A table with ten indexes writes noticeably slower than one with two.

Two special index types are worth knowing. A unique index also enforces uniqueness, which is how UNIQUE constraints work under the hood. A partial index, such as CREATE INDEX idx_unbilled ON orders (order_id) WHERE amount IS NULL, indexes only the rows you search often; SQLite and PostgreSQL support it, and MySQL does not.

Common mistakes with SQL indexes

• Indexing every column. Writes slow down and the planner gains little. Index for the queries you actually run.
• Wrong column order in a composite index. Put the column you always filter by with = first, then the range or sort column.
• Functions on indexed columns. WHERE LOWER(name) = 'asha verma' ignores a plain index on name. Use an expression index (SQLite, PostgreSQL, MySQL 8.0.13+) or store a cleaned column.
• Forgetting foreign key indexes in PostgreSQL and SQLite. Joins on orders.customer_id scan without one.
• Guessing instead of measuring. Check the plan with EXPLAIN before and after. The broader topic of SQL query optimisation builds on this habit.

Knowing why a report is slow, and fixing it with one well-chosen index, is a skill that sets analysts apart in interviews.

The Old Palasia data analytics course that teaches query tuning at Debugshala covers it with real query plans, and SQL performance MCQs on Debugshala Test let you check what you have learned. Next in the series, SQL views show how to save a query and reuse it like a table.

Key takeaways

• An index is a sorted structure, usually a B-tree, that turns a full scan into a quick lookup.
• Primary keys and UNIQUE columns are indexed automatically; foreign keys often are not.
• Use EXPLAIN (or EXPLAIN QUERY PLAN in SQLite) to see whether a query scans or searches.
• Composite indexes follow the leftmost prefix rule; a covering index answers a query on its own.
• Indexes slow down writes, and functions or leading wildcards on a column usually prevent their use.