Understanding Database Indexes: The Key to Lightning-Fast Queries

Imagine searching for a specific word in a 1,000-page book without an index. You'd have to read every single page until you found it. That's exactly what your database does without indexes - and it's painfully slow.

Let's fix that.

What Is an Index?

A database index is like the index at the back of a book. Instead of scanning every row to find your data, the database can jump directly to the right location.

Without an index:

• Database scans every row (full table scan)
• 1 million rows = 1 million checks
• Slow and resource-intensive

With an index:

• Database looks up the location instantly
• 1 million rows = a few lookups
• Fast and efficient

How Indexes Work

Under the hood, most indexes use a data structure called a B-tree (balanced tree). Think of it like a well-organized filing system:

                    [M]
                   /   \
              [D-H]     [P-T]
             /  |  \    /  |  \
          [A-C][E-G][I-L][N-O][Q-S][U-Z]

When you search for "Smith", the database:

1. Checks the root: S comes after M, go right
2. Checks the next level: S is in P-T range
3. Checks the leaf: Found in Q-S section
4. Returns the exact row location

This takes just 3 steps instead of scanning thousands of rows.

Creating Your First Index

Creating an index is straightforward:

-- Create a simple index on the email column
CREATE INDEX idx_user_email ON users(email);

-- Now this query is fast
SELECT * FROM users WHERE email = '[email protected]';

Before the index: scans all users After the index: instant lookup

Types of Indexes

Single-Column Index

Best for queries that filter on one column:

CREATE INDEX idx_created_at ON orders(created_at);

-- This query benefits from the index
SELECT * FROM orders WHERE created_at > '2026-01-01';

Composite Index (Multi-Column)

For queries that filter on multiple columns:

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- This query uses the full index
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'completed'
  AND created_at > '2026-01-01';

Important: Column order matters! The index above works for:

• WHERE user_id = 123 (uses first column)
• WHERE user_id = 123 AND status = 'completed' (uses first two)
• WHERE user_id = 123 AND status = 'completed' AND created_at > '2026-01-01' (uses all three)

But NOT for:

• WHERE status = 'completed' (skips first column)
• WHERE created_at > '2026-01-01' (skips first two columns)

Unique Index

Ensures no duplicate values and speeds up lookups:

CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Prevents duplicate emails AND speeds up searches
INSERT INTO users (email) VALUES ('[email protected]'); -- Works
INSERT INTO users (email) VALUES ('[email protected]'); -- Error!

Full-Text Index

For searching within text content:

-- MySQL
CREATE FULLTEXT INDEX idx_article_content ON articles(title, body);

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization');

Partial Index (PostgreSQL)

Index only some rows to save space:

-- Only index active users
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

When to Create Indexes

Good Candidates for Indexes

1. Primary keys - Automatically indexed
2. Foreign keys - Speed up JOINs
3. Columns in WHERE clauses - Filter conditions
4. Columns in ORDER BY - Sorting
5. Columns in GROUP BY - Aggregations

-- Common query patterns that benefit from indexes
SELECT * FROM users WHERE email = ?;           -- Index on email
SELECT * FROM orders WHERE user_id = ?;        -- Index on user_id
SELECT * FROM products ORDER BY price DESC;    -- Index on price
SELECT status, COUNT(*) FROM orders GROUP BY status; -- Index on status

When NOT to Create Indexes

1. Small tables - Full scans are fast enough
2. Columns with few unique values - Low cardinality (like boolean flags)
3. Frequently updated columns - Index maintenance slows writes
4. Columns rarely used in queries - Wasted space

Checking If Your Index Is Being Used

Use EXPLAIN to see how your query runs:

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Look for:

• type: ref or type: const = Good! Using index
• type: ALL = Bad! Full table scan
• key: idx_user_email = Shows which index is used
• rows: 1 = Only examining 1 row (efficient)

The Cost of Indexes

Indexes aren't free. Every index:

1. Takes up disk space - Can be significant for large tables
2. Slows down writes - INSERT, UPDATE, DELETE must update indexes
3. Needs maintenance - Can become fragmented over time

Finding Unused Indexes

In MySQL:

SELECT * FROM sys.schema_unused_indexes;

In PostgreSQL:

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Remove indexes that aren't being used to improve write performance.

Index Optimization Tips

1. Use Covering Indexes

Include all columns needed by your query to avoid accessing the table:

-- Create a covering index
CREATE INDEX idx_order_summary ON orders(user_id, status, total);

-- This query reads only from the index, never touches the table
SELECT user_id, status, total FROM orders WHERE user_id = 123;

2. Mind the Column Order

Put the most selective columns first in composite indexes:

-- If user_id narrows down to fewer rows than status
CREATE INDEX idx_user_status ON orders(user_id, status);  -- Better
CREATE INDEX idx_status_user ON orders(status, user_id);  -- Worse

3. Don't Wrap Indexed Columns in Functions

-- Bad: Can't use index
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- Good: Uses index
SELECT * FROM users
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

4. Keep Indexes Lean

Shorter indexes are faster:

-- For very long text columns, index just a prefix
CREATE INDEX idx_title ON articles(title(50));

Real-World Example

Let's optimize a slow query step by step.

The problem query:

SELECT o.id, o.total, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

Step 1: Check the execution plan

EXPLAIN SELECT ...
-- Shows: type: ALL, rows: 500000 (full table scan!)

Step 2: Add appropriate indexes

-- Index for the WHERE and ORDER BY
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Index for the JOIN
CREATE INDEX idx_orders_user ON orders(user_id);

Step 3: Verify improvement

EXPLAIN SELECT ...
-- Shows: type: range, rows: 150, key: idx_orders_status_date

Query time: 3.2 seconds -> 0.02 seconds

Summary

Conclusion

Indexes are one of the most powerful tools for database performance. The right indexes can make your queries hundreds or even thousands of times faster.

Start by identifying your slowest queries, add appropriate indexes, and verify with EXPLAIN. Your users (and your servers) will thank you.

Want to Visualize Your Index Performance?

LunoDB shows you query execution plans visually, making it easy to spot missing indexes and optimize your queries. Download LunoDB and see how your indexes are performing.