SQL indexes are the single biggest lever you have for making a slow database query fast, and in this post you will watch one index turn a query from 72 milliseconds into 0.04 milliseconds on a one million row table. You will also learn to read EXPLAIN QUERY PLAN line by line, design a schema that stays fast as it grows, keep your data honest with transactions, and kill the N+1 query problem that quietly wrecks so many apps.

“An index is a promise you make to your future queries. Break it, and every read pays the price.”

Last Updated: July 2026 | Tested on: Python 3.14.6 | Difficulty: Advanced | Reading Time: 22 minutes

Here is a picture everyone knows. You are handed a 900 page phone book and asked to find the one person named Aditi Kulkarni. If the book is sorted by name, you flip to the K section, then the Ku pages, and you have her in a few seconds. Now imagine the same book with the names in completely random order. Your only option is to start on page one and read every single line until you hit hers. That sorted order is exactly what SQL indexes give you, and the random pile is what you get without one.

A database stores your rows on disk in roughly the order they were inserted, which for lookups is the random pile. An index is a second, sorted structure that the engine keeps beside the table, so it can jump straight to the rows you asked for instead of reading all of them. Everything in this post is tested on SQLite, which ships inside Python, but the ideas carry over unchanged to PostgreSQL, MySQL, and every other relational database. The syntax shifts a little; the thinking does not.

The diagram sums up the whole post. On the left, a full scan reads every row until it finds a match, so the cost grows with the size of the table. On the right, a B-tree index takes about three hops from the root down to the exact leaf, no matter how big the table gets. That gap is why SQL indexes make the same query below run roughly 1700 times faster.

Why One Index Makes a Query 1700x Faster

Let us prove the phone book story with real numbers. The script below builds a table of one million orders, each with a unique customer code, then searches for a single customer both before and after adding an index. It prints the query plan each time and times the lookup. This runs on plain Python with no installs, because SQLite is built in.

📄 build_and_explain.py: one million rows, one query, before and after an index

import sqlite3, time, os, random

DB = "shop.db"
if os.path.exists(DB):
    os.remove(DB)

conn = sqlite3.connect(DB)
cur = conn.cursor()
cur.execute("""
    CREATE TABLE orders (
        id       INTEGER PRIMARY KEY,
        customer TEXT NOT NULL,
        city     TEXT NOT NULL,
        total    REAL NOT NULL
    )
""")

cities = ["Pune", "Mumbai", "Delhi", "Nagpur", "Nashik"]
names  = ["Aditi", "Anvay", "Aviraj", "Anvi", "Rahul", "Viraj"]
random.seed(7)
rows = (
    (i,
     f"{random.choice(names)}-{i:07d}",   # a unique customer code per order
     random.choice(cities),
     round(random.uniform(50, 5000), 2))
    for i in range(1, 1_000_001)
)
cur.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", rows)
conn.commit()

cur.execute("SELECT COUNT(*) FROM orders")
print("Rows loaded:", cur.fetchone()[0])

QUERY  = "SELECT id, city, total FROM orders WHERE customer = ?"
TARGET = ("Aditi-0784512",)

def timed(q, param, n=7):
    times = []
    for _ in range(n):
        t0 = time.perf_counter()
        cur.execute(q, param)
        cur.fetchall()
        times.append(time.perf_counter() - t0)
    return min(times)

cur.execute("EXPLAIN QUERY PLAN " + QUERY, TARGET)
print("\nPlan BEFORE index:")
for r in cur.fetchall():
    print(" ", r[-1])
before = timed(QUERY, TARGET)
print(f"Best time BEFORE index: {before*1000:.2f} ms")

cur.execute("CREATE INDEX idx_orders_customer ON orders(customer)")
conn.commit()

cur.execute("EXPLAIN QUERY PLAN " + QUERY, TARGET)
print("\nPlan AFTER index:")
for r in cur.fetchall():
    print(" ", r[-1])
after = timed(QUERY, TARGET)
print(f"Best time AFTER index:  {after*1000:.3f} ms")
print(f"Speedup: about {before/after:.0f}x faster")

conn.close()

▶ Output

Rows loaded: 1000000

Plan BEFORE index:
  SCAN orders
Best time BEFORE index: 71.83 ms

Plan AFTER index:
  SEARCH orders USING INDEX idx_orders_customer (customer=?)
Best time AFTER index:  0.042 ms
Speedup: about 1690x faster

What happened here: Before the index, the plan says SCAN orders, which is the database admitting it has to walk all one million rows to find one customer. After a single CREATE INDEX line, the plan changes to SEARCH orders USING INDEX, and the same lookup drops from about 72 milliseconds to 0.04 milliseconds. Nothing about the query changed. We only gave the engine a sorted structure to jump through, and it went roughly 1700 times faster. That is the entire value proposition of an index in one run.

The sorted structure under the hood is a B-tree (a balanced tree kept a few levels deep). Each node points to a narrower range of keys, so finding a value takes a number of steps that grows with the logarithm of the row count, not the row count itself. Double the table to two million rows and a scan takes twice as long, while the index lookup barely moves, because it only added one more level to the tree. That is why SQL indexes matter more, not less, as your data grows.

Reading EXPLAIN QUERY PLAN Line by Line

EXPLAIN QUERY PLAN is the database telling you, in advance, how it intends to answer your query. Think of it like asking a delivery rider to describe their route before they leave. If they say “I will knock on every door in the city until I find the address”, you know the trip will be slow. If they say “I will use the map to go straight there”, you can relax. You never have to guess whether an index is helping; you just read the plan.

There are only a few words you need to recognise, and you already saw the two that matter most. Here is how to read them.

• SCAN: the engine reads every row in the table. Fine for tiny tables, a red flag on big ones.
• SEARCH … USING INDEX: the engine jumped through an index to the matching rows. This is what you want on a hot query.
• USING COVERING INDEX: even better, every column the query needs lives in the index itself, so the engine never touches the table at all.
• USE TEMP B-TREE: the engine had to build a throwaway structure to sort or group, often a hint that an index on the ORDER BY or GROUP BY column would help.

The workflow in real life is simple. Find a slow query, put EXPLAIN QUERY PLAN in front of it, and if you see SCAN on a large table, that is your invitation to add an index on the column in the WHERE clause. Then run it again and confirm the plan flipped to SEARCH. PostgreSQL and MySQL use the same idea with their own EXPLAIN and EXPLAIN ANALYZE commands, which add estimated and real row counts, but the habit of reading the plan before you optimise is identical everywhere.

Schema Design That Scales

Indexes make reads fast, but a good schema is what keeps your data correct in the first place. The core idea is normalization, which is a formal word for a simple habit: store each fact exactly once. If a customer changes their city, you want to update it in one place, not hunt through ten thousand order rows that each repeated it. Picture a restaurant menu. You do not reprint the chef’s full biography on every dish; you write it once and let each dish point back to it.

In practice, aiming for third normal form (3NF) covers almost everything you need. The plain English version is: every column in a table should describe the row’s key, the whole key, and nothing but the key. You split repeating groups into their own tables and connect them with keys. A categories table holds each category once, and a products table points at it with a category_id. That pointer is a foreign key, and along with a few constraints it becomes a guardrail the database enforces for you. The script below shows those guardrails rejecting bad data.

📄 constraints.py: foreign keys and constraints reject bad rows automatically

import sqlite3

conn = sqlite3.connect("store.db")
conn.execute("PRAGMA foreign_keys = ON")   # SQLite needs this switched on per connection

conn.executescript("""
    CREATE TABLE categories (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL UNIQUE
    );
    CREATE TABLE products (
        id          INTEGER PRIMARY KEY,
        name        TEXT NOT NULL,
        price       REAL NOT NULL CHECK (price > 0),
        category_id INTEGER NOT NULL,
        FOREIGN KEY (category_id) REFERENCES categories(id)
    );
""")
conn.execute("INSERT INTO categories VALUES (1, 'Groceries')")
conn.commit()

def try_insert(label, sql, params):
    try:
        conn.execute(sql, params)
        conn.commit()
        print(f"OK      {label}")
    except sqlite3.IntegrityError as e:
        print(f"BLOCKED {label}: {e}")

try_insert("valid product",         "INSERT INTO products (name, price, category_id) VALUES (?, ?, ?)", ("Paneer", 320.0, 1))
try_insert("negative price",        "INSERT INTO products (name, price, category_id) VALUES (?, ?, ?)", ("Free Lunch", -5.0, 1))
try_insert("missing category (99)", "INSERT INTO products (name, price, category_id) VALUES (?, ?, ?)", ("Ghost Item", 50.0, 99))
try_insert("duplicate category",    "INSERT INTO categories (id, name) VALUES (?, ?)", (2, "Groceries"))

print("Products that made it in:", conn.execute("SELECT name FROM products").fetchall())
conn.close()

▶ Output

OK      valid product
BLOCKED negative price: CHECK constraint failed: price > 0
BLOCKED missing category (99): FOREIGN KEY constraint failed
BLOCKED duplicate category: UNIQUE constraint failed: categories.name

What happened here: Only the valid product slipped through. The CHECK (price > 0) blocked a negative price, the foreign key blocked a product pointing at a category that does not exist, and the UNIQUE constraint blocked a duplicate category name. You did not write a single line of validation logic; the schema did it. One SQLite quirk worth remembering: foreign keys are only enforced when you run PRAGMA foreign_keys = ON on the connection, whereas PostgreSQL enforces them always. Constraints like these are cheaper than any application check, because a bad row can never enter the table from any client, script, or console.

Normalization is the default, but it is not a religion. Sometimes you deliberately denormalize, which means storing a copy of a value to avoid an expensive join on a very hot read path. A common example is caching an order’s total on the order row instead of summing its line items every time a dashboard loads. The trade is real: you gain read speed and you take on the job of keeping the copy in sync. The rule of thumb is to normalize first, measure, and denormalize only the specific spots where the numbers tell you to.

Transactions and ACID Without the Jargon

A transaction is a group of changes that either all happen or none happen. The classic example is moving money: subtract from one account, add to another. If the power dies between those two steps, you never want the money to vanish into thin air. The four letters of ACID describe the promise a transaction makes. Atomicity means all or nothing. Consistency means the rules (your constraints) always hold. Isolation means concurrent transactions do not step on each other. Durability means once it is committed, a crash cannot lose it.

The word that trips people up is isolation, so let us make it concrete with a race condition. Say ten background jobs each try to withdraw 10 from an account that starts at 1000, so the correct end balance is 900. The unsafe version reads the balance into Python, waits a moment, subtracts, and writes it back. That read then write gap is where two jobs both read the same starting number and one overwrites the other, a bug called a lost update. The safe version hands the arithmetic to the database in one atomic UPDATE.

📄 race.py: lost updates without a transaction, correct with an atomic UPDATE

import sqlite3, threading, time, os

def setup(db):
    if os.path.exists(db):
        os.remove(db)
    with sqlite3.connect(db) as c:
        c.execute("CREATE TABLE account (id INTEGER PRIMARY KEY, balance INTEGER)")
        c.execute("INSERT INTO account VALUES (1, 1000)")

WORKERS = 10   # each withdraws 10, so the correct end balance is 900

def withdraw_unsafe(db):
    conn = sqlite3.connect(db, timeout=10)
    bal = conn.execute("SELECT balance FROM account WHERE id = 1").fetchone()[0]
    time.sleep(0.01)                 # window where another thread reads the same value
    conn.execute("UPDATE account SET balance = ? WHERE id = 1", (bal - 10,))
    conn.commit()
    conn.close()

def withdraw_safe(db):
    conn = sqlite3.connect(db, timeout=10)
    conn.execute("UPDATE account SET balance = balance - 10 WHERE id = 1")
    conn.commit()
    conn.close()

def run(worker, db):
    setup(db)
    threads = [threading.Thread(target=worker, args=(db,)) for _ in range(WORKERS)]
    for t in threads: t.start()
    for t in threads: t.join()
    with sqlite3.connect(db) as c:
        return c.execute("SELECT balance FROM account WHERE id = 1").fetchone()[0]

print("Start balance 1000, ten withdrawals of 10, correct end balance = 900")
print("Unsafe read-modify-write end balance:", run(withdraw_unsafe, "bank_unsafe.db"))
print("Safe atomic UPDATE end balance:      ", run(withdraw_safe,   "bank_safe.db"))

▶ Output

Start balance 1000, ten withdrawals of 10, correct end balance = 900
Unsafe read-modify-write end balance: 990
Safe atomic UPDATE end balance:       900

What happened here: The unsafe run ended at 990, not 900. All ten threads read the balance as 1000 during that sleep window, each computed 990, and each wrote 990 back, so nine of the ten withdrawals were lost. The safe run ended at exactly 900, because SET balance = balance - 10 reads and writes in a single atomic step that SQLite serialises with a write lock, leaving no gap for another thread to sneak in. The lesson is bigger than banking: whenever new value depends on the current value, do the math inside one SQL statement, or wrap the read and write in an explicit transaction, rather than round-tripping through Python.

The N+1 Query Problem and the JOIN That Kills It

The N+1 query problem is the most common performance bug in database code, and it hides in the most innocent looking loop. You fetch a list of authors with one query, then loop over them and fire one more query per author to get their posts. Four authors means one query plus four, which is five. Four thousand authors means four thousand and one. Each query is a separate round trip to the database, and the round trips, not the data, are what kill you.

The real-life version is grocery shopping. The N+1 way is to drive to the store, buy one onion, drive home, then drive back for one tomato, and repeat for every item on the list. The fix is obvious the moment you say it out loud: bring the whole list and buy everything in one trip. In SQL that one trip is a JOIN. The script counts the statements each approach runs so you can see the difference, not just take my word for it.

📄 nplus1.py: counting the queries an N+1 loop runs versus a single JOIN

import sqlite3, os

DB = "blog.db"
if os.path.exists(DB): os.remove(DB)

conn = sqlite3.connect(DB)
conn.executescript("""
    CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE posts   (id INTEGER PRIMARY KEY, author_id INTEGER, title TEXT);
""")
conn.executemany("INSERT INTO authors VALUES (?, ?)",
                 [(1,"Aditi"), (2,"Anvay"), (3,"Aviraj"), (4,"Anvi")])
conn.executemany("INSERT INTO posts VALUES (?, ?, ?)",
                 [(i, (i % 4) + 1, f"Post {i}") for i in range(1, 13)])
conn.commit()

class Counter:
    def __init__(self, conn): self.conn, self.n = conn, 0
    def q(self, sql, params=()):
        self.n += 1
        return self.conn.execute(sql, params).fetchall()

# N+1: one query for authors, then one query PER author for their posts
c1 = Counter(conn)
authors = c1.q("SELECT id, name FROM authors")                    # 1 query
for aid, name in authors:
    c1.q("SELECT title FROM posts WHERE author_id = ?", (aid,))   # +1 each
print(f"N+1 approach:  {c1.n} queries for {len(authors)} authors")

# Fixed: a single JOIN pulls everything in one round trip
c2 = Counter(conn)
rows = c2.q("""
    SELECT authors.name, posts.title
    FROM authors
    JOIN posts ON posts.author_id = authors.id
    ORDER BY authors.name
""")
print(f"JOIN approach: {c2.n} query for the same {len(rows)} rows")

conn.close()

▶ Output

N+1 approach:  5 queries for 4 authors
JOIN approach: 1 query for the same 12 rows

What happened here: The loop ran 5 queries for just 4 authors, and that number grows one for one with your data. The JOIN pulled the exact same 12 rows in a single query. On a small local database you might not feel the difference, but add network latency to a real server and 4001 queries becomes seconds of wasted time while 1 query stays instant. This is also where object relational mappers earn their keep. In SQLAlchemy, the same N+1 trap appears when you access a related attribute inside a loop, and the fix is to eager load with joinedload() or selectinload(), which is the Object-Relational Mapping (ORM) asking for that single round trip on your behalf.

One more schema topic belongs here: migrations. As an app grows, your tables change, and you need a repeatable, version controlled way to alter them without hand editing production. The go to tool for SQLAlchemy projects, at the time of writing, is Alembic, which records each schema change as a small script you can apply forward or roll back. If you work in Django instead, the built in Django migrations system does the same job with makemigrations and migrate. The tool you pick matters less than the principle: schema changes should be code you can review and replay, never a manual ALTER TABLE typed live into a server.

Composite Indexes, Selectivity, and When Indexes Hurt

Two ideas separate people who add SQL indexes at random from people who add the right ones. The first is selectivity, which measures how many rows a column value narrows you down to. A column full of unique values, like an email or a customer code, is highly selective and makes a great index, because a lookup lands on one row. A column with only a few possible values, like a yes or no flag, is barely selective; an index on it still points at half the table, so the engine often ignores it and scans anyway. Index the columns you filter on that carve the data down to a small slice.

The second idea is column order in a composite index, an index built on more than one column. A composite index on (city, total) is sorted by city first, and only by total within each city, exactly like a contact list sorted by last name then first name. That order means the index can answer queries that start with city, but not queries that only mention total. This is the leftmost prefix rule, and it is a favourite interview question. The script proves it.

📄 composite.py: the leftmost prefix rule of a (city, total) index

import sqlite3, os, random

DB = "comp.db"
if os.path.exists(DB): os.remove(DB)
conn = sqlite3.connect(DB)
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT, total REAL)")
random.seed(1)
cities = ["Pune", "Mumbai", "Delhi", "Nagpur", "Nashik"]
conn.executemany(
    "INSERT INTO orders (city, total) VALUES (?, ?)",
    [(random.choice(cities), round(random.uniform(50, 5000), 2)) for _ in range(200_000)]
)
conn.execute("CREATE INDEX idx_city_total ON orders(city, total)")  # city first, then total
conn.commit()

def plan(sql, params):
    rows = conn.execute("EXPLAIN QUERY PLAN " + sql, params).fetchall()
    print(sql.strip())
    for r in rows:
        print("   ->", r[-1])
    print()

plan("SELECT id FROM orders WHERE city = ? AND total > ?", ("Pune", 4000))  # uses both columns
plan("SELECT id FROM orders WHERE city = ?", ("Pune",))                     # uses the first column
plan("SELECT id FROM orders WHERE total > ?", (4000,))                      # skips the first: no index
conn.close()

▶ Output

SELECT id FROM orders WHERE city = ? AND total > ?
   -> SEARCH orders USING COVERING INDEX idx_city_total (city=? AND total>?)

SELECT id FROM orders WHERE city = ?
   -> SEARCH orders USING COVERING INDEX idx_city_total (city=?)

SELECT id FROM orders WHERE total > ?
   -> SCAN orders

What happened here: The first two queries both used the composite index, because they filter on city, which is the leading column. The third query filters only on total, the second column, so the index is useless to it and the plan falls back to SCAN orders. It is the same reason a phone book sorted by last name is no help when all you know is a first name. When you build a composite index, put the column you filter on most, and by equality, first.

As a bonus, the first two plans say COVERING INDEX, meaning the id, city, and total the query needed were all available in the index, so the table itself was never read.

So when do SQL indexes hurt? Every index is a second structure the database must update on every insert, update, and delete. Ten indexes on a table means ten little B-trees to rewrite each time a row changes, which slows down writes and eats disk. On a write heavy table, a pile of rarely used indexes is pure overhead. The balance is straightforward: index the columns your real queries filter and sort on, and drop indexes that EXPLAIN shows nothing is using.

Common Mistakes

Mistake 1: Indexing everything, or indexing nothing

Both extremes cost you. No SQL indexes means every lookup is a full scan that gets slower as the table grows. An index on every column means writes crawl and disk usage balloons, while most of those indexes never get used. The fix is to let evidence drive it: run EXPLAIN QUERY PLAN on your actual slow queries, add indexes where you see SCAN on a large table, and remove indexes nothing touches.

Mistake 2: Wrapping an indexed column in a function

A query like WHERE lower(customer) = 'aditi' cannot use a plain index on customer, because the index stores the raw values, not their lowercased form. The engine would have to compute lower() for every row, which is a scan. Either store the data already normalised, or create an index on the exact expression you query. The general rule: keep the indexed column bare on the left side of the comparison.

Mistake 3: Doing math in Python that the database should do

The race condition earlier came from reading a value into Python, changing it, and writing it back. Any time the new value depends on the current one, that round trip opens a window for lost updates. Push the work into a single SQL statement like SET balance = balance - 10, or wrap the read and write in one transaction, so the database keeps it atomic.

Best Practices

• DO read EXPLAIN QUERY PLAN before optimising. If you do not see SCAN on a big table, an index is not your problem.
• DO index the columns you filter and join on, favouring highly selective ones like ids, emails, and codes.
• DO put the equality column first in a composite index, then the range column, to satisfy the leftmost prefix rule.
• DO let constraints (NOT NULL, UNIQUE, CHECK, foreign keys) guard your data at the source instead of trusting application code alone.
• DON’T add an index to every column. Each one slows writes and costs disk, and most go unused.
• DON’T solve a list with a loop of queries. Reach for a JOIN, or eager loading in your ORM, to avoid the N+1 trap.

Conclusion

You now have the tools that separate a query that scales from one that falls over. SQL indexes turn a full scan into a targeted search and bought us a 1700x speedup on a real one million row table. EXPLAIN QUERY PLAN takes the guesswork out by showing you SCAN versus SEARCH before you change a thing. A normalized schema with foreign keys and constraints keeps your data correct without a line of validation code, and transactions keep it correct under concurrency. Finally, spotting the N+1 pattern and reaching for a JOIN saves thousands of needless round trips.

The single habit that ties it all together is to measure, not guess. Read the plan, add the index the plan asks for, confirm the plan changed, and move on. Do that consistently and your databases stay fast whether they hold a thousand rows or a hundred million. For the full path from Python basics through the AI and ML chapters, visit the Python + AI/ML tutorial series home.

Frequently Asked Questions

Interview Questions on SQL Indexes

Interviewers rarely ask for definitions. They ask what happens in situations like these.

Q: A query on a large table is slow. Walk me through how you would diagnose and fix it.

Start with EXPLAIN QUERY PLAN (or EXPLAIN ANALYZE on PostgreSQL) to see how the engine runs it. If the plan shows a full SCAN on a big table for a query that filters on one column, add an index on that column and confirm the plan flips to SEARCH ... USING INDEX. If it is a range or sort query, check whether a composite index that matches the filter and order helps. Only after the plan and timings agree do I consider bigger changes like denormalizing a hot path.

Q: What is index selectivity and why does it matter?

Selectivity is how finely a column value narrows the rows. A unique column like an email is highly selective, so an index lookup lands on one row and is very fast. A low cardinality column like a boolean flag points at a large fraction of the table, so the optimiser often ignores an index on it and scans anyway, because reading half the table through an index is slower than a plain scan. You get the most from indexes on selective columns.

Q: You have a composite index on (a, b, c). Which queries can use it?

The leftmost prefix rule applies. Queries filtering on a, on a and b, or on a, b, and c can use it. A query filtering only on b, or only on c, or on b and c without a, cannot, because the index is sorted by a first. That is why you order composite index columns by how you actually query them, equality columns before range columns.

Q: Explain a transaction and the ACID properties in your own words.

A transaction is a group of changes that all commit together or not at all. Atomicity is that all or nothing behaviour. Consistency means the database never violates its constraints, moving from one valid state to another. Isolation means concurrent transactions do not see each other’s half finished work, which prevents bugs like lost updates. Durability means a committed transaction survives a crash. In practice I lean on atomic single statement updates and explicit transactions to get isolation right under concurrency.

Q: How would you find and fix an N+1 query problem in a real codebase?

I look for a query inside a loop, which is the tell. Query logging or an ORM’s echo mode makes it obvious: you see the same statement fire once per item. The fix is to fetch the related data in one shot, with a JOIN in raw SQL or eager loading like joinedload() or selectinload() in SQLAlchemy, so the whole set comes back in a single round trip instead of N of them.

Q: When would you denormalize a schema on purpose?

When a specific read path is hot and the join or aggregation behind it is measurably expensive. A common case is caching a computed total on a parent row so a dashboard does not re-sum child rows on every load. I only do it after profiling, and I accept the cost, which is keeping the copy in sync on every write, usually with a transaction or a trigger. Normalize by default, denormalize the proven hot spots.

Go deeper: SQL reference (SQLite) covers every edge case of this topic.

Related Posts

Previous: SQL GROUP BY, CTEs, and Window Functions, Explained

Next: Python SQLite: Database and CRUD Operations

Series Home: Python + AI/ML Tutorial Series