Understanding Database Indexes: The Key to Performance

Imagine you're looking for a specific topic in a 1,000-page textbook.

• Without an index: You have to flip through every single page until you find it. (Full Table Scan)
• With an index: You go to the back of the book, find the topic alphabetically, and jump straight to the page number. (Index Lookup)

This is exactly how database indexes work. They are special data structures (usually B-Trees) that help the database find rows without scanning the entire table.

Creating an Index

The syntax is simple:

CREATE INDEX index_name ON table_name (column_name);

Let's see it in action. We'll use EXPLAIN QUERY PLAN to see how the database executes our query.

Before Indexing: Full Table Scan

Result: You'll likely see SCAN TABLE users or similar. This means the database looked at every row.

After Indexing: Search Table using Index

Now let's add an index on the email column.

Result: You should see SEARCH TABLE users USING INDEX idx_users_email. This is much faster!

Composite Indexes (Multi-column)

You can index multiple columns at once. This is useful when you often query by multiple fields together.

CREATE INDEX idx_name_dept ON employees (department, salary);

Important Rule: The order matters! This index helps queries like:

• WHERE department = 'Sales'
• WHERE department = 'Sales' AND salary > 50000

But it WON'T help:

• WHERE salary > 50000 (because salary is the second part of the index, like trying to find someone in a phone book by their first name only).

How the Database Decides Whether to Use an Index

Beginners often hear "add an index" as if that guarantees a faster query. It doesn't. The optimizer still has to decide whether the index is actually cheaper than scanning the table.

In practice, databases look at things like:

• How many rows match the filter. If your query returns half the table, scanning may be cheaper than bouncing between an index and the table.
• Whether the leading columns line up with the query. An index on (department, salary) helps department = 'Sales', but not a filter that starts only on salary.
• Whether the query also needs sorting. A well-ordered index can satisfy both WHERE and ORDER BY.
• Table size. On tiny tables, a scan is often faster than using an index.

This is why EXPLAIN QUERY PLAN matters so much. Do not assume an index is being used just because you created one.

Query Patterns That Usually Benefit from Indexes

Indexes are most useful when they support a repeated access pattern, not a one-off query.

Exact lookups

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

This is the classic index win: find one row or a very small set of rows quickly.

Range filters

SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-02-01';

Date and numeric ranges can benefit heavily from indexes, especially on large event or transaction tables.

Sorting and pagination

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

If you frequently run queries like this, an index such as (customer_id, created_at) can help the database filter first and then return rows in the desired order without an extra sort step.

JOINs

If one table joins on customer_id, order_id, or another key repeatedly, that join column usually deserves an index. This is why foreign keys are a common indexing candidate.

Common Reasons an Index Is Ignored

If your index exists but the plan still shows a scan, check these common issues:

1. The predicate is not selective enough. Filtering on a boolean like is_active = true often matches too many rows.
2. The query wraps the indexed column in a function. For example, WHERE LOWER(email) = '[email protected]' may prevent normal index usage unless you create a functional index.
3. The index column order is wrong. A composite index only helps when the query starts from the leftmost part of the index.
4. The table is too small. The optimizer may correctly choose a scan.
5. The query asks for many columns. Sometimes the engine decides the cost of jumping from the index back to the table is too high.

A Practical Workflow for Choosing Indexes

Use this process instead of guessing:

1. Find the slow query.
2. Run EXPLAIN or EXPLAIN QUERY PLAN.
3. Identify the filter, join, and sort columns.
4. Add the smallest useful index that matches the real access pattern.
5. Re-run the plan and compare before/after execution time.
6. Watch write performance and storage cost before adding more.

That last step matters. Indexes are infrastructure, not decoration. Every extra index adds maintenance overhead on every write.

The Cost of Indexing

If indexes make reads faster, why not index every column?

Because indexes slow down writes.

Every time you INSERT, UPDATE, or DELETE a row, the database has to update the table AND all the indexes.

• Read-heavy apps: More indexes are fine.
• Write-heavy apps: Be careful with too many indexes.

When to Create an Index

✅ Do Index:

• Primary Keys (usually indexed automatically)
• Foreign Keys (for JOIN performance)
• Columns frequently used in WHERE, ORDER BY, and GROUP BY clauses
• Columns with high cardinality (many unique values, like email or UUID)

❌ Don't Index:

• Small tables (scanning 10 rows is faster than loading an index)
• Columns with low cardinality (e.g., gender or is_active boolean - the index won't narrow down the search enough to be worth it)
• Columns that are frequently updated but rarely queried

Real-World Examples of Good Index Design

Here are a few patterns that show up constantly in production systems:

• User lookup: users(email) for login and account recovery flows.
• Recent orders for one customer: orders(customer_id, created_at) for dashboards and account history pages.
• Analytics events by day: events(event_date) or (event_type, event_date) when most reporting filters by date first.
• Parent-child relationship: order_items(order_id) to make joins back to orders fast.

Notice what these have in common: they map to recurring business questions. The best indexes are not "technically possible" indexes. They are indexes that support your most valuable queries.

Common Production Scenarios Where Indexing Pays Off

It is easier to choose indexes well when you think in application scenarios instead of abstract rules.

Scenario 1: Customer dashboard with recent activity

Imagine an account page that always shows the latest 20 orders for one customer:

SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

This is a perfect indexing candidate because the query shape is stable and repeated. A composite index like (customer_id, created_at) helps the database:

• find one customer's rows quickly
• return them in the right order
• avoid a larger sort step

Scenario 2: Admin search by email or external ID

Support and operations teams often search for one user, one order, or one account by a unique identifier. This is where single-column indexes shine because the match set is tiny and the business value of a fast lookup is high.

Typical examples:

• users(email)
• users(external_id)
• orders(order_number)

These indexes are simple, selective, and easy to justify.

Scenario 3: High-volume event tables filtered by time

Analytics and audit tables often grow very quickly. Queries usually ask for a recent window:

SELECT event_type, created_at, actor_id
FROM audit_events
WHERE created_at >= '2026-04-01'
  AND created_at < '2026-04-02';

In this case, indexing the time column, or a composite pattern like (event_type, created_at) when the workload regularly filters by both, can make the difference between scanning millions of rows and jumping straight to the relevant slice.

Scenario 4: JOIN-heavy transactional queries

Indexes are not only about WHERE. They are also about how tables connect.

If your application frequently joins:

• orders.customer_id -> customers.customer_id
• order_items.order_id -> orders.order_id
• payments.order_id -> orders.order_id

then those join keys are often high-value indexing candidates. Otherwise the optimizer may be forced into slower join strategies or large scans just to match rows.

When Indexes Disappoint Even Though They Exist

One of the most confusing moments in performance tuning is adding an index and seeing no improvement.

That usually happens for one of these reasons:

The query shape fights the index

Examples:

• WHERE YEAR(created_at) = 2026
• WHERE LOWER(email) = '[email protected]'
• WHERE name LIKE '%smith%'

The index may be fine. The predicate is the real problem.

The query returns too many rows

If half the table matches, the optimizer may correctly choose a scan because using the index would still require reading a huge portion of the table.

The selected columns are too wide

Sometimes the engine uses the index to find candidates, but still has to visit many table rows to fetch all requested columns. This is where narrower SELECT lists or covering-index strategies start to matter.

The wrong column order was indexed

A composite index is only useful when it matches the real filter and sort pattern. This is why "we already have an index" is not enough information. You need the right index, in the right order, for the right query.

Frequently Asked Questions

Should I index every foreign key?

In many transactional systems, yes, or at least strongly consider it. Foreign keys often participate in joins, lookups, and parent-child navigation. If those access patterns matter, indexing the foreign key column is usually worthwhile.

Why did my write performance get worse after adding indexes?

Because every INSERT, UPDATE, and DELETE now has more structures to maintain. Indexes trade write cost for read speed. That trade is often worth it, but it is never free.

How do I know whether a composite index should be (a, b) or (b, a)?

Start from the real query pattern, not from the schema. Look at which column the query filters on first, how selective it is, and whether the query also sorts by another column. The best order is the one that matches the repeated access path.

Are indexes useful for small tables?

Often not. On small tables, scanning can be cheaper than using an index. This is normal and not a bug. Indexing becomes more valuable as the table grows and as the access pattern repeats.

If I add an index and the plan still shows a scan, did the index fail?

Not necessarily. It may mean the optimizer correctly judged the scan to be cheaper, or it may mean the query shape prevents the index from being effective. Check the predicate, column order, selectivity, and returned columns before assuming the index is useless.

Related Articles

• Reading SQL Execution Plans for a deeper look at how query planners choose scans, seeks, and joins.
• SQL Optimization Techniques for broader performance tuning beyond indexing.
• SQL Anti-Patterns: The Silent Performance Killers in Your Queries for query shapes that can make good indexes ineffective.

Key Takeaways

1. Indexes speed up reads but slow down writes.
2. Use EXPLAIN QUERY PLAN to verify if your index is being used.
3. Composite indexes are powerful, but column order is critical (Leftmost Prefix Rule).
4. Don't over-index; focus on the columns you actually query.

Mastering indexes is the single most effective way to improve database performance. A well-placed index can turn a 10-second query into a 10-millisecond one!