SQL Tutorial #6: Indexes and Performance — Making Queries Fast

Your queries work. But they are slow. A simple SELECT takes seconds instead of milliseconds. On a table with millions of rows, it could take minutes.

The fix is almost always the same: add an index.

In the previous article, you learned about window functions. Now you will learn how to make all your queries run fast.

What Is an Index?

Think of a book’s index at the back. If you want to find “window functions” in a 500-page book, you have two options:

1. Read every page from start to finish until you find it (slow)
2. Look in the index, find the page number, and go directly there (fast)

A database index works the same way. Without an index, the database reads every row in the table to find what you want. This is called a full table scan or sequential scan. With an index, the database jumps directly to the matching rows.

How Indexes Work: B-Tree

Most databases use a data structure called a B-tree for indexes. You do not need to understand the details, but here is the basic idea:

A B-tree is like a sorted tree. Data is organized in a way that lets the database find any value in a few steps, no matter how large the table is.

                    [50]
                   /    \
              [25]        [75]
             /    \      /    \
          [10] [30]  [60]  [90]

To find the value 30:

1. Start at 50 → go left (30 < 50)
2. Arrive at 25 → go right (30 > 25)
3. Found 30

That is 3 steps. Without the tree, you might need to check thousands of rows.

The numbers:

• A table with 1,000 rows: about 10 steps with an index
• A table with 1,000,000 rows: about 20 steps with an index
• Without an index: you check all 1,000,000 rows

Creating an Index

CREATE INDEX idx_books_author_id ON books(author_id);

This creates an index on the author_id column of the books table. Now queries that filter by author_id will be fast.

Naming convention: Start with idx_, followed by the table name and column name. This makes it easy to know what each index does.

Drop an index:

DROP INDEX idx_books_author_id;

List all indexes (PostgreSQL):

SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE schemaname = 'public';

List all indexes (SQLite):

.indexes

When to Add Indexes

Add indexes on columns that appear in:

1. WHERE Clauses

-- This query benefits from an index on published_year
SELECT title FROM books WHERE published_year > 2000;

CREATE INDEX idx_books_published_year ON books(published_year);

2. JOIN Conditions

-- This query benefits from an index on author_id
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

CREATE INDEX idx_books_author_id ON books(author_id);

Foreign key columns should almost always have an index. Primary keys already have one automatically.

3. ORDER BY Columns

-- This query benefits from an index on price
SELECT title, price FROM books ORDER BY price DESC LIMIT 10;

CREATE INDEX idx_books_price ON books(price);

4. Columns Used in GROUP BY

-- Grouping by author_id benefits from an index
SELECT author_id, COUNT(*) FROM books GROUP BY author_id;

When NOT to Add Indexes

Indexes are not free. They come with costs:

1. Small tables

If a table has fewer than a few hundred rows, a full table scan is already fast. An index adds overhead without a real benefit.

2. Columns that change very often

Every time you INSERT, UPDATE, or DELETE a row, the database must also update the index. If a column changes constantly, the index maintenance slows down writes.

3. Columns with very few unique values

An index on a boolean column (is_active with only true/false) is usually not helpful. The database still has to read half the table.

4. Tables that are mostly written to

If a table gets far more writes than reads, indexes slow things down. Log tables are a common example.

EXPLAIN — Understanding Query Plans

How do you know if a query uses an index? Use EXPLAIN.

PostgreSQL:

EXPLAIN ANALYZE SELECT title FROM books WHERE published_year = 1949;

Seq Scan on books  (cost=0.00..1.09 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=1)
  Filter: (published_year = 1949)
  Rows Removed by Filter: 6
Planning Time: 0.065 ms
Execution Time: 0.030 ms

This tells you:

• Seq Scan: Sequential scan — the database reads every row. No index is being used.
• rows=1: It found 1 matching row.
• Rows Removed by Filter: 6: It checked 7 rows total (6 did not match).

Now add an index and check again:

CREATE INDEX idx_books_year ON books(published_year);
EXPLAIN ANALYZE SELECT title FROM books WHERE published_year = 1949;

Index Scan using idx_books_year on books  (cost=0.13..8.15 rows=1 width=32) (actual time=0.025..0.026 rows=1 loops=1)
  Index Cond: (published_year = 1949)
Planning Time: 0.080 ms
Execution Time: 0.040 ms

Now it says Index Scan instead of Seq Scan. The database used the index to find the row directly.

SQLite:

EXPLAIN QUERY PLAN SELECT title FROM books WHERE published_year = 1949;

SEARCH books USING INDEX idx_books_year (published_year=?)

SEARCH ... USING INDEX means the index is being used. SCAN TABLE means a full table scan.

Reading Query Plans

The key terms to look for:

Composite Indexes

A composite index covers multiple columns. The order of columns matters.

CREATE INDEX idx_books_author_year ON books(author_id, published_year);

This index helps queries that filter by:

• author_id alone
• author_id AND published_year

But it does NOT help queries that filter by published_year alone. Think of it like a phone book sorted by last name, then first name. You can look up “Smith” quickly. You can look up “Smith, John” quickly. But looking up everyone named “John” (regardless of last name) requires scanning the whole book.

-- Uses the composite index (matches left-to-right)
SELECT * FROM books WHERE author_id = 3;
SELECT * FROM books WHERE author_id = 3 AND published_year > 1940;

-- Does NOT use the composite index
SELECT * FROM books WHERE published_year > 1940;

Rule of thumb: Put the most selective column first (the one that narrows down the most rows).

Partial Indexes

A partial index only covers rows that match a condition. This saves space and speeds up specific queries.

-- Index only in-stock books
CREATE INDEX idx_books_in_stock ON books(price) WHERE in_stock = 1;

This index is smaller than indexing all books. Queries that filter by in_stock = 1 and sort or filter by price will use this index.

-- Uses the partial index
SELECT title, price FROM books WHERE in_stock = 1 ORDER BY price;

-- Does NOT use the partial index
SELECT title, price FROM books WHERE in_stock = 0 ORDER BY price;

Partial indexes are available in PostgreSQL and SQLite. MySQL does not support them.

Expression Indexes

You can create an index on an expression, not just a column.

-- Index on lowercase title (useful for case-insensitive search)
CREATE INDEX idx_books_title_lower ON books(LOWER(title));

Now this query can use the index:

SELECT * FROM books WHERE LOWER(title) = 'animal farm';

Without the expression index, the database cannot use a regular index on title because of the LOWER() function call.

Common Performance Mistakes

1. SELECT * — Fetching All Columns

-- Bad: fetches all columns, even if you only need title
SELECT * FROM books WHERE price < 10;

-- Good: only fetches what you need
SELECT title FROM books WHERE price < 10;

SELECT * makes the database read more data from disk. It also prevents “index only scans” where the database can answer the query using only the index, without touching the table at all.

2. Missing Indexes on JOIN Columns

-- Slow if books.author_id has no index
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

Foreign key columns need indexes. Primary keys get them automatically, but foreign keys do not (in most databases). Always add an index on your foreign key columns.

3. Functions on Indexed Columns

-- Cannot use an index on published_year
SELECT * FROM books WHERE YEAR(published_year) = 1949;

-- Can use an index on published_year
SELECT * FROM books WHERE published_year = 1949;

When you wrap a column in a function, the database cannot use the index on that column. Rewrite the query to avoid the function, or create an expression index.

4. N+1 Queries

This happens in application code, not in raw SQL. Instead of one query that fetches all the data you need, you run one query to get a list, then one query per item in the list.

-- Bad: N+1 queries
Query 1: SELECT id FROM authors;  -- returns 5 authors
Query 2: SELECT * FROM books WHERE author_id = 1;
Query 3: SELECT * FROM books WHERE author_id = 2;
Query 4: SELECT * FROM books WHERE author_id = 3;
Query 5: SELECT * FROM books WHERE author_id = 4;
Query 6: SELECT * FROM books WHERE author_id = 5;
-- Total: 6 queries

-- Good: one query with a JOIN
Query 1: SELECT a.name, b.title FROM authors a JOIN books b ON a.id = b.author_id;
-- Total: 1 query

One query is almost always faster than many queries, even with indexes.

5. LIKE with a Leading Wildcard

-- Cannot use an index (starts with wildcard)
SELECT * FROM books WHERE title LIKE '%Farm';

-- Can use an index (wildcard at the end only)
SELECT * FROM books WHERE title LIKE 'Animal%';

LIKE '%something' forces a full table scan. The database must check every row. For full-text search, use specialized tools like PostgreSQL’s tsvector or a search engine like Elasticsearch.

A Practical Optimization Example

Let us say you have a large orders table and this slow query:

SELECT
    c.name,
    COUNT(*) AS order_count,
    SUM(b.price * o.quantity) AS total_spent
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN books b ON o.book_id = b.id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 10;

Step 1: Check the query plan

EXPLAIN ANALYZE [the query above];

Look for Seq Scans on large tables.

Step 2: Add indexes on JOIN and WHERE columns

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_book_id ON orders(book_id);
CREATE INDEX idx_orders_date ON orders(order_date);

Step 3: Check the query plan again

The Seq Scans should be replaced by Index Scans.

Step 4: Consider a composite index

If you always filter by order_date and join on customer_id:

CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);

This single index can help with both the WHERE and the JOIN.

How Many Indexes Is Too Many?

There is no fixed number. But keep these guidelines in mind:

• Every index slows down INSERT, UPDATE, and DELETE operations
• A table with 10 indexes means every write updates 10 indexes
• Start with indexes on primary keys (automatic), foreign keys, and the most common WHERE/ORDER BY columns
• Add more indexes only when you see slow queries in EXPLAIN ANALYZE
• Remove indexes that are never used

Check for unused indexes (PostgreSQL):

SELECT
    indexrelname AS index_name,
    idx_scan AS times_used
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

If an index has times_used = 0 for months, you can safely drop it.

Common Mistakes

1. Adding indexes on every column

More indexes do not always mean faster queries. They slow down writes and use disk space. Only index columns that your queries actually filter, join, or sort on.

2. Ignoring EXPLAIN

Guessing which query is slow is a waste of time. Use EXPLAIN ANALYZE to see exactly what the database is doing. Let the data guide your optimization.

3. Premature optimization

Do not add indexes before you have a performance problem. Start with correct queries. Optimize when you measure slowness.

What You Learned

In this article, you learned:

• Indexes let the database find rows without scanning the entire table
• B-trees are the most common index structure
• CREATE INDEX adds an index to a column
• Add indexes on WHERE, JOIN, ORDER BY, and GROUP BY columns
• EXPLAIN ANALYZE shows how the database runs your query
• Composite indexes cover multiple columns (left-to-right)
• Partial indexes cover a subset of rows
• SELECT *, functions on columns, and leading wildcards hurt performance
• N+1 queries are a common application-level problem

Related Articles

• Window Functions — Analytics Without GROUP BY — previous article
• SQL Cheat Sheet — quick reference for all SQL commands

What’s Next?

In the next article, you will set up PostgreSQL — the most popular open-source database. You will learn Docker-based setup, psql, PostgreSQL-specific features like JSONB and arrays, table constraints, migrations, and backups.