Composite index and column order in SQL - Deep Dive

Overview - Composite index and column order

What is it?

A composite index is a database index that uses multiple columns together to speed up data searches. Instead of indexing just one column, it combines two or more columns into a single index. The order of these columns in the index matters because it affects how the database can use the index to find data quickly.

Why it matters

Without composite indexes, searching for data involving multiple columns can be slow, especially in large databases. Composite indexes help databases find rows faster when queries filter or sort by multiple columns. If the column order is wrong, the index might not be used efficiently, causing slower queries and more waiting time.

Where it fits

Before learning about composite indexes, you should understand what a simple index is and how it speeds up searches on one column. After this, you can learn about query optimization and how databases choose which indexes to use for complex queries.

Mental Model

Core Idea

A composite index is like a multi-key map where the order of keys determines how quickly you find what you need.

Think of it like...

Imagine a filing cabinet organized first by year, then by month, then by day. If you look for documents from a specific year and month, you find them quickly. But if you try to find documents by day alone, it’s harder because the cabinet isn’t sorted that way.

Composite Index Structure:
┌─────────────┬─────────────┬─────────────┐
│ Column 1    │ Column 2    │ Column 3    │
├─────────────┼─────────────┼─────────────┤
│ Value A1    │ Value B1    │ Value C1    │
│ Value A1    │ Value B2    │ Value C2    │
│ Value A2    │ Value B1    │ Value C3    │
└─────────────┴─────────────┴─────────────┘

The database uses this order to quickly narrow down searches.

Build-Up - 7 Steps

1

Foundation

What is a database index

Concept: Introduce the idea of an index as a tool to speed up data searches.

A database index is like a shortcut that helps the database find rows faster without scanning the whole table. It works like an index in a book, pointing to where information is located.

Result

Queries that use indexed columns run faster because the database looks up the index instead of scanning every row.

Understanding indexes is key because composite indexes build on this basic idea to handle multiple columns.

2

Foundation

Single-column index basics

Concept: Explain how an index on one column works and when it helps.

When you create an index on one column, the database sorts and stores values from that column with pointers to the full rows. Searching by that column becomes quick because the database can jump directly to matching values.

Result

Queries filtering or sorting by that single column become much faster.

Knowing single-column indexes helps you see why combining columns into one index can be powerful.

3

Intermediate

Composite index definition and use

Concept: Introduce composite indexes as indexes on multiple columns together.

A composite index stores combined values of two or more columns in a specific order. This helps queries that filter or sort by these columns together. For example, an index on (last_name, first_name) helps find people by last and first name quickly.

Result

Queries using the first column or the first and second columns in the index can use the composite index efficiently.

Composite indexes extend the power of single-column indexes to multiple columns, improving multi-column query speed.

4

Intermediate

Why column order matters in composite indexes

🤔Before reading on: Do you think the order of columns in a composite index affects all queries equally? Commit to your answer.

Concept: Explain how the order of columns in the index affects which queries can use it.

The database uses the composite index starting from the first column. Queries filtering on the first column can use the index fully. Queries filtering only on the second or third columns cannot use the index efficiently unless the first columns are also filtered.

Result

An index on (A, B) helps queries filtering on A or on both A and B, but not on B alone.

Knowing that the index is like a path starting from the first column helps you design indexes that match your query patterns.

5

Intermediate

Prefix matching and index usage

🤔Before reading on: Can a composite index be used if a query filters only on the second column? Commit to yes or no.

Concept: Introduce the idea of prefix matching where the index can be used only if the query filters on the leading columns in order.

The database can use a composite index only if the query filters on a prefix of the indexed columns. For example, an index on (A, B, C) can be used if the query filters on A, or A and B, or A, B, and C. But filtering only on B or C alone won't use the index.

Result

Queries that do not filter on the first column(s) in the index will not benefit from the composite index.

Understanding prefix matching prevents wasted indexes and helps write queries that use indexes effectively.

6

Advanced

Composite index impact on sorting and grouping

🤔Before reading on: Do you think composite indexes can speed up sorting by multiple columns? Commit to yes or no.

Concept: Explain how composite indexes can help not only filtering but also sorting and grouping operations.

Because composite indexes store data in a sorted order based on the column sequence, the database can use them to quickly sort or group results by those columns without extra work. For example, an index on (date, category) helps sort results by date and category efficiently.

Result

Queries with ORDER BY or GROUP BY on the indexed columns run faster using the composite index.

Knowing that composite indexes help sorting and grouping expands their usefulness beyond just filtering.

7

Expert

Index selectivity and column order optimization

🤔Before reading on: Should the most unique column always be first in a composite index? Commit to your answer.

Concept: Discuss how choosing the order of columns in a composite index depends on data uniqueness and query patterns, not just putting the most unique column first.

While putting the most selective (unique) column first can reduce search space, sometimes queries filter first on less selective columns. The best order balances query patterns and column uniqueness. For example, if queries always filter on column A first, even if less unique, A should be first in the index.

Result

Proper column order in composite indexes leads to better query performance and less wasted index space.

Understanding the tradeoff between selectivity and query patterns is key to designing effective composite indexes in real systems.

Under the Hood

A composite index stores combined values of multiple columns in a sorted data structure, usually a B-tree. The index entries are ordered first by the first column, then by the second, and so on. When a query uses the index, the database traverses the tree starting from the first column's value, narrowing down to matching rows. If the query does not filter on the leading columns, the index cannot be used efficiently because the tree traversal depends on that order.

Why designed this way?

Composite indexes were designed to speed up multi-column queries by storing combined keys in a single structure. The column order reflects how data is sorted and searched, which is simpler and faster than multiple separate indexes. Alternatives like multiple single-column indexes require complex merging and are less efficient. The prefix matching rule comes from the sorted nature of B-trees, which must start searching from the first key.

Composite Index B-tree Structure:

Root
 ├─ Column1=ValueA
 │   ├─ Column2=ValueB
 │   │   ├─ Column3=ValueC -> Row pointers
 │   │   └─ Column3=ValueD -> Row pointers
 │   └─ Column2=ValueE
 │       └─ Column3=ValueF -> Row pointers
 └─ Column1=ValueG
     └─ Column2=ValueH
         └─ Column3=ValueI -> Row pointers

Search starts at root by Column1, then Column2, then Column3.

Myth Busters - 4 Common Misconceptions

Quick: Can a composite index on (A, B) speed up queries filtering only on B? Commit to yes or no.

Common Belief: A composite index on (A, B) will speed up queries filtering only on B.

Reality: The index cannot be used efficiently if the query filters only on B without filtering on A first.

Why it matters: Believing this leads to creating useless indexes and slow queries because the database ignores the index for such filters.

Quick: Does the order of columns in a composite index not affect query performance? Commit to yes or no.

Common Belief: The order of columns in a composite index does not matter; any order works the same.

Reality: The order is critical because the index is sorted by columns in sequence, affecting which queries can use it.

Why it matters: Ignoring column order causes indexes to be ineffective, wasting storage and slowing queries.

Quick: Is it always best to put the most unique column first in a composite index? Commit to yes or no.

Common Belief: The most unique column should always be first in a composite index for best performance.

Reality: The best order depends on query patterns; sometimes less unique columns come first if queries filter on them first.

Why it matters: Misordering columns can cause the index not to be used, leading to slower queries.

Quick: Can composite indexes speed up sorting and grouping operations? Commit to yes or no.

Common Belief: Composite indexes only help filtering, not sorting or grouping.

Reality: Composite indexes can speed up sorting and grouping if the order matches the query's ORDER BY or GROUP BY columns.

Why it matters: Missing this means missing opportunities to optimize query performance.

Expert Zone

1

Composite indexes can be partially used if the query filters on a prefix of the columns, but the database may still scan more rows if later columns are not filtered.

2

Some databases support index skip scans that allow using composite indexes even when the first column is not filtered, but this is less efficient and not universally supported.

3

Including columns with low cardinality (few unique values) early in the index can sometimes reduce index effectiveness, but if queries filter on them first, it might still be optimal.

When NOT to use

Composite indexes are not ideal when queries filter on many different column combinations unpredictably; in such cases, multiple single-column indexes or full-text indexes might be better. Also, if the table is write-heavy, large composite indexes can slow down inserts and updates.

Production Patterns

In production, composite indexes are designed based on query logs to match frequent multi-column filters and sorts. DBAs monitor index usage and adjust column order or drop unused indexes. Covering indexes, which include extra columns beyond the key columns, are often combined with composite indexes to avoid accessing the table data.

Connections

B-tree data structure

Composite indexes are implemented using B-trees which organize data in sorted order.

Understanding B-trees helps explain why column order and prefix matching rules exist for composite indexes.

Query optimization

Composite indexes are a key tool that query optimizers use to speed up multi-column queries.

Knowing how query optimizers choose indexes helps in designing composite indexes that are actually used.

Library cataloging systems

Both organize information by multiple keys in a specific order to find items quickly.

Seeing composite indexes like library catalogs clarifies why order matters and how combined keys speed up searches.

Common Pitfalls

#1 Creating a composite index with columns in the wrong order for query patterns.

Wrong approach:

CREATE INDEX idx_wrong_order ON users (email, last_name); -- but queries filter mostly by last_name first

Correct approach:

CREATE INDEX idx_correct_order ON users (last_name, email); -- matches query filter order

Root cause:

Misunderstanding that the index column order must match the order of columns used in query filters.

#2 Expecting a composite index to speed up queries filtering only on the second column.

Wrong approach:

SELECT * FROM orders WHERE product_id = 10; -- index on (customer_id, product_id) exists but not used

Correct approach:

SELECT * FROM orders WHERE customer_id = 5 AND product_id = 10; -- uses composite index

Root cause:

Not knowing that composite indexes require filtering on leading columns to be used.

#3 Creating too many large composite indexes causing slow writes.

Wrong approach:

CREATE INDEX idx_large ON sales (region, category, date, product, customer); -- very large index

Correct approach:

Create smaller, targeted composite indexes based on actual query needs, e.g., (region, date) or (category, product).

Root cause:

Not balancing read performance with write overhead and storage costs.

Key Takeaways

Composite indexes combine multiple columns into one index to speed up queries filtering or sorting by those columns together.

The order of columns in a composite index is crucial because the database uses the index starting from the first column in sequence.

Queries can only use a composite index efficiently if they filter on a prefix of the indexed columns, starting from the first column.

Composite indexes can also speed up sorting and grouping operations when the order matches the query's ORDER BY or GROUP BY clauses.

Designing effective composite indexes requires understanding query patterns, column uniqueness, and balancing read and write performance.

Practice

1. What is the main benefit of using a composite index in a database?

easy

A. It encrypts the data for security.

B. It reduces the size of the database file.

C. It automatically backs up the database.

D. It speeds up queries filtering on multiple columns together.

Solution

1. Step 1: Understand what a composite index does

   A composite index is created on multiple columns to help the database find rows faster when queries filter on those columns together.

2. Step 2: Compare options to the definition

   Only It speeds up queries filtering on multiple columns together. correctly describes the benefit of speeding up queries using multiple columns. Other options describe unrelated features.

3. Final Answer:

   It speeds up queries filtering on multiple columns together. -> Option D

4. Quick Check:

   Composite index = speeds up multi-column queries [OK]

Hint: Composite index helps multi-column filters run faster [OK]

Common Mistakes:

• Thinking it reduces database size
• Confusing indexing with backup or encryption
• Assuming it speeds up single-column queries only

2. Which of the following is the correct syntax to create a composite index on columns last_name and first_name in a table employees?

easy

A. CREATE INDEX idx_name ON employees last_name, first_name;

B. CREATE INDEX idx_name ON employees (last_name, first_name);

C. CREATE INDEX idx_name (last_name, first_name) ON employees;

D. CREATE INDEX idx_name ON employees WHERE last_name, first_name;

Solution

1. Step 1: Recall correct CREATE INDEX syntax

   The correct syntax is: CREATE INDEX index_name ON table_name (column1, column2);

2. Step 2: Match options to syntax

   CREATE INDEX idx_name ON employees (last_name, first_name); matches the correct syntax exactly. Options A, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   CREATE INDEX idx_name ON employees (last_name, first_name); -> Option B

4. Quick Check:

   CREATE INDEX ... ON table (columns) [OK]

Hint: Use parentheses around columns after ON table [OK]

Common Mistakes:

• Omitting parentheses around columns
• Placing columns before ON keyword
• Using WHERE instead of column list

3. Given a composite index on (city, age) in a table users, which query will use the index efficiently?

medium

A. SELECT * FROM users WHERE age = 25 AND city = 'Boston';

B. SELECT * FROM users WHERE age > 30;

C. SELECT * FROM users WHERE city = 'Boston' AND age > 30;

D. SELECT * FROM users WHERE city = 'Boston' ORDER BY age DESC;

Solution

1. Step 1: Understand index column order usage

   A composite index on (city, age) can be used efficiently if the query filters on city first, then age.

2. Step 2: Analyze each query

   SELECT * FROM users WHERE city = 'Boston' AND age > 30; filters on city and age, matching index order. SELECT * FROM users WHERE age > 30; filters only on age (second column), so index is less useful. SELECT * FROM users WHERE age = 25 AND city = 'Boston'; filters age first, then city, which is less efficient. SELECT * FROM users WHERE city = 'Boston' ORDER BY age DESC; filters city but only orders by age, which may use index partially.

3. Final Answer:

   SELECT * FROM users WHERE city = 'Boston' AND age > 30; -> Option C

4. Quick Check:

   Index (city, age) best for city then age filters [OK]

Hint: Filter on first index column before others [OK]

Common Mistakes:

• Assuming index works well if filtering only second column
• Ignoring column order in composite index
• Thinking ORDER BY always uses index fully

4. You created a composite index on (department, salary) but your query filtering only on salary is slow. What is the likely cause?

medium

A. The index column order starts with department, so filtering only salary can't use it well.

B. The index is corrupted and needs rebuilding.

C. The salary column data type is incompatible with indexing.

D. The database does not support composite indexes.

Solution

1. Step 1: Recall how composite index column order affects usage

   Composite indexes work best when filtering starts with the first column. Filtering only on the second column (salary) won't use the index efficiently.

2. Step 2: Evaluate options

   The index column order starts with department, so filtering only salary can't use it well. correctly explains the cause. Options A, B, and D are unlikely or incorrect because the index exists and salary can be indexed normally.

3. Final Answer:

   The index column order starts with department, so filtering only salary can't use it well. -> Option A

4. Quick Check:

   Filter must start with first index column [OK]

Hint: Filter on first index column to use composite index [OK]

Common Mistakes:

• Assuming any column in index can be filtered efficiently alone
• Blaming index corruption without checking usage
• Ignoring column order importance

5. You have a table orders with columns customer_id, order_date, and status. You often run queries filtering by customer_id and sorting by order_date. Which composite index will optimize these queries best?

hard

A. CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

B. CREATE INDEX idx_date_customer ON orders (order_date, customer_id);

C. CREATE INDEX idx_status_customer ON orders (status, customer_id);

D. CREATE INDEX idx_order ON orders (order_date);

Solution

1. Step 1: Identify query filter and sort columns

   Queries filter by customer_id and sort by order_date, so index should start with customer_id and include order_date next.

2. Step 2: Match index column order to query pattern

   CREATE INDEX idx_customer_date ON orders (customer_id, order_date); matches this order (customer_id, order_date), allowing efficient filtering and sorting. CREATE INDEX idx_date_customer ON orders (order_date, customer_id); reverses order, less efficient for filtering. Options A, B, and C do not match query needs.

3. Final Answer:

   CREATE INDEX idx_customer_date ON orders (customer_id, order_date); -> Option A

4. Quick Check:

   Index columns match filter then sort order [OK]

Hint: Index columns in filter then sort order [OK]

Common Mistakes:

• Reversing column order in composite index
• Indexing unrelated columns
• Using single-column index when composite needed