Single-Column vs Composite Indexes in SQL

What They Are, When to Use Them, and a Myth We Need to Bust

Image generated by author using ChatGPT

Indexes are one of the most misunderstood topics in SQL. Developers know they make queries faster — but how, when, and which type of index to choose is often unclear.

Introduction

1. What Is a Single-Column Index?

A single-column index is an index created on one column in the table.

CREATE INDEX idx_users_email 
ON users(email);

Think of it as the index at the back of a book:

• You look up a word (email value),
• The index tells you exactly where to find it.

Single-Column Indexes work best when the query filters primarily on one column:

SELECT *
FROM users
WHERE email = 'john@example.com';

Or when we join on that column:

JOIN orders o ON u.user_id = o.user_id;

2. What Is a Composite Index?

A composite index indexes multiple columns together.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

Composite Indexes follow the “Leftmost Prefix Rule”.

This is critical to understand:

An index on (A, B, C) can be used for queries that start with:

• A
• A, B
• A, B, C

But NOT for:

• B
• C
• B, C
• C, A

Order matters. A lot.

Composite Indexes Always Outperform Single Indexes on Big Tables (100M+ rows)?

Short answer: ❌ No. This is a myth.

Many engineers believe:

“If your table has more than 100 million rows, just use Composite Indexes because they are faster.”

This is FALSE, and can actually slow your database down.

1. What Actually Determines Index Performance?

It’s not table size. It’s query patterns and selectivity.

SELECT *
FROM orders
WHERE customer_id = 10
  AND order_date >= '2023-01-01';

A composite index will help only if it matches the query — (customer_id, order_date). If your index is the wrong order — (order_date, customer_id), the query may not use it at all.

2. Why the “100M rows rule” is wrong

Even with 100M rows:

• A single-column index on customer_id may still be optimal
• A composite index might not be used
• Table size does not change how indexes fundamentally work

The index’s ability to filter rows efficiently is what matters.

Example: When Single-Column Is Better

-- Query filters only on filmid
WHERE filmid = 'F123'

Composite index exists on (actorid, filmid), but since the query does not filter by the first column (actorid), the composite index becomes useless.

So even with 500M rows, this query will not use the composite index. A single-column index on filmid would be faster.

3. Why Composite Indexes appear to outperform on very large datasets

Suppose you have 500M rows:

• The column Country has 3 distinct values
• The column State has 50 distinct values
• The column City has 10,000 distinct values

If you filter on:

WHERE country = 'USA' AND state = 'Texas' AND city = 'Austin';

Then:

Index(country) → 1/3 of table → ~166M rows returned (useless)

Index(country, state) → 1/150 of table → ~3.3M rows (better)

Index(country, state, city) → maybe a few thousand rows (excellent)

The improvement is not because composite indexes are “faster.” It is because they narrow down the data much more effectively.

Best Practices and Common Pitfalls with Composite Indexes

Beginner mistakes with composite indexes can tank performance. Here’s what to watch out for.

1. Don’t assume a composite index replaces single indexes

If you create:

INDEX (customer_id, order_date)

This does NOT replace:

INDEX (order_date)

Queries filtering only on order_date will not use the composite index.

2. Order matters more than most people think

Right order:

(customer_id, order_date)

Wrong order:

(order_date, customer_id)

These two indexes behave very differently.

Put columns in this order:

1. Column used most for filtering

2. Column with highest selectivity

3. Column used for sorting

4. Put equality conditions before range conditions

5. Avoid indexing low-cardinality columns

Bad candidates:

• is_active
• gender
• country_code (if <5 values)
• status (success/failed)

These do not reduce row count enough to justify index overhead.

4. Don’t over-index

Indexes speed up reads, but slow down:

• INSERT
• UPDATE
• DELETE

Each index must be maintained. If your table has 10 indexes, every write becomes slower.

5. Use EXPLAIN ANALYZE to validate every index

Never guess. Always measure.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 101 AND order_date > '2024-01-01';

The execution plan will tell you:

• Whether the index is used
• How many rows were scanned
• Whether a sort happened
• Whether a table scan is occurring
• Whether your index order is correct

Conclusion

Composite indexes are powerful — but only when used correctly.

Key takeaways:

• Single-column indexes are simple and effective for queries filtering on one column.
• Composite indexes help when queries depend on multiple columns together.
• Table size (100M+ rows) does NOT automatically make composite indexes faster.
• The query pattern, index order, and selectivity matter far more.
• Avoid beginner traps: wrong column order, low-cardinality columns, and over-indexing.

If you design indexes based on how your queries actually behave — not based on myths — your database will scale smoothly even with billions of rows.

Thank you for your time and if you would like to share your thoughts/opinions, please leave a comment :)