Composite indexes in MySQL - Deep Dive

Overview - Composite indexes

What is it?

A composite index is a special kind of index in a database that uses more than one column to speed up searches. Instead of looking at just one column, it looks at a combination of columns together. This helps the database find data faster when queries involve multiple columns. It is like having a multi-key to open a specific lock quickly.

Why it matters

Without composite indexes, searching for data using multiple columns would be slower because the database would have to check many rows one by one. This can make websites or apps feel slow, especially when dealing with lots of data. Composite indexes solve this by letting the database jump directly to the right rows using the combined columns, making everything faster and smoother.

Where it fits

Before learning composite indexes, you should understand what simple indexes are and how they help search in one column. After mastering composite indexes, you can learn about index optimization, covering how to choose the best columns and order for indexes, and how databases use indexes internally.

Mental Model

Core Idea

A composite index is like a multi-part address that helps the database find rows quickly by using multiple columns together as a single key.

Think of it like...

Imagine looking for a book in a library. Instead of searching by just the author's name or just the book title, you use both together: author and title. This combined information narrows down the search much faster than using either alone.

Composite Index Structure:
┌───────────────┐
│ Composite Key │
│ (Column A +   │
│  Column B)    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Index Entries │
│ Sorted by     │
│ Column A then │
│ Column B      │
└───────────────┘

Query uses columns A and B together to quickly find matching rows.

Build-Up - 7 Steps

1

Foundation

What is a database index

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

A database index is like a shortcut or a table of contents for your data. Instead of looking through every row in a table, the database uses the index to jump directly to the rows that match your search. For example, if you have a list of names, an index on the name column helps find a specific name quickly.

Result

Queries using indexed columns run faster because the database avoids scanning the whole table.

Understanding indexes as shortcuts helps you see why databases need them to handle large amounts of data efficiently.

2

Foundation

Simple single-column indexes

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

A single-column index is built on just one column, like a phone book sorted by last name. When you search using that column, the database can quickly find matching rows. But if your query uses other columns, this index might not help much.

Result

Queries filtering by the indexed column become much faster, but queries using other columns may still be slow.

Knowing the limits of single-column indexes prepares you to understand why composite indexes are needed.

3

Intermediate

Introducing composite indexes

🤔Before reading on: do you think a composite index works the same as multiple single-column indexes combined? Commit to your answer.

Concept: Show how indexes can cover multiple columns together to speed up queries involving those columns.

A composite index combines two or more columns into one index. It sorts data first by the first column, then by the second, and so on. This helps queries that filter or sort by these columns in the same order. For example, an index on (last_name, first_name) helps find people by last name and then first name quickly.

Result

Queries using the first column or the first and second columns together run faster with a composite index.

Understanding that composite indexes are ordered combinations of columns reveals why the order of columns matters.

4

Intermediate

How column order affects composite indexes

🤔Before reading on: do you think switching the order of columns in a composite index changes which queries it helps? Commit to your answer.

Concept: Explain that the order of columns in a composite index determines which queries can use it efficiently.

The composite index works like a phone book sorted by last name then first name. If you switch the order to first name then last name, queries filtering by last name first won't benefit. The database can only use the index efficiently if the query filters on the leftmost columns in the index.

Result

Queries filtering on the first column or a leftmost prefix of the index columns are fast; others may not use the index.

Knowing the importance of column order helps you design indexes that match your query patterns.

5

Intermediate

Using composite indexes for sorting and filtering

Concept: Show how composite indexes can speed up both filtering (WHERE) and sorting (ORDER BY) when columns match the index order.

If your query filters by columns in the index and sorts by the same columns in the same order, the database can use the composite index to avoid extra sorting steps. For example, an index on (city, age) helps queries filtering by city and sorting by age run faster.

Result

Queries with matching filter and sort columns run faster because the index covers both operations.

Understanding that composite indexes can optimize sorting as well as filtering helps you write faster queries.

6

Advanced

Partial use of composite indexes

🤔Before reading on: do you think a composite index on (A, B, C) can speed up queries filtering only on B and C? Commit to your answer.

Concept: Explain that composite indexes can only be used starting from the first column and moving right, so skipping the first column limits index use.

A composite index on (A, B, C) can speed up queries filtering on A alone, or A and B, or A, B, and C together. But if a query filters only on B and C without A, the index usually cannot help. This is because the index is sorted first by A, so the database can't jump directly to B or C values without knowing A.

Result

Queries missing the leftmost column in filters may not benefit from the composite index.

Knowing this limitation prevents wasted indexes and helps design queries and indexes that work well together.

7

Expert

Covering indexes and index-only scans

🤔Before reading on: do you think a composite index can make the database avoid reading the full table? Commit to your answer.

Concept: Introduce the idea that if a composite index contains all columns a query needs, the database can answer the query using only the index.

A covering index is a composite index that includes all columns used in a query's SELECT, WHERE, and ORDER BY clauses. When this happens, the database can get all needed data from the index itself without looking at the full table rows. This makes queries much faster because it reads less data.

Result

Queries using covering composite indexes run faster by avoiding extra data reads.

Understanding covering indexes reveals how composite indexes can optimize performance beyond just filtering.

Under the Hood

Composite indexes are stored as sorted data structures (like B-trees) where entries are ordered first by the first column, then by the second, and so on. When a query uses the leftmost columns, the database can quickly navigate the tree to find matching rows. If the query uses columns out of order or skips the first column, the index cannot be used efficiently because the sorting order does not help locate data directly.

Why designed this way?

This design balances speed and storage. Sorting by multiple columns in order allows fast lookups for common query patterns without creating many separate indexes. Alternatives like separate indexes for each column would require combining results, which is slower. The leftmost prefix rule simplifies index structure and lookup logic.

Composite Index B-Tree Structure:

Root
 │
 ├─ Sorted by Column A
 │    ├─ Within A=Value1: Sorted by Column B
 │    │    ├─ Within B=Value1: Sorted by Column C
 │    │    └─ Within B=Value2: Sorted by Column C
 │    └─ Within A=Value2: Sorted by Column B
 │         ├─ Within B=Value1: Sorted by Column C
 │         └─ Within B=Value2: Sorted by Column C

Queries use this order to quickly find matching rows.

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 on B alone.

Reality: Composite indexes only help queries filtering on the leftmost column(s). Filtering only on B without A usually cannot use the index efficiently.

Why it matters: Believing otherwise leads to creating useless indexes that waste space and do not improve query speed.

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

Common Belief: The order of columns in a composite index does not affect which queries it helps.

Reality: The order is crucial; the index helps queries filtering on the leftmost columns first. Changing order changes which queries benefit.

Why it matters: Ignoring column order causes slow queries and poor index design.

Quick: Can a composite index always replace multiple single-column indexes? Commit to yes or no.

Common Belief: A composite index can always replace multiple single-column indexes for all queries.

Reality: Composite indexes help queries using columns in order but may not help queries filtering on only some columns. Sometimes multiple single-column indexes or other indexes are needed.

Why it matters: Relying only on composite indexes can cause some queries to run slower if they don't match the index pattern.

Quick: Does adding more columns to a composite index always improve performance? Commit to yes or no.

Common Belief: Adding more columns to a composite index always makes queries faster.

Reality: Adding unnecessary columns can make the index larger and slower to update, sometimes hurting performance.

Why it matters: Over-indexing wastes storage and slows down data changes.

Expert Zone

1

Composite indexes can be used for index merge optimization in some databases, but MySQL prefers leftmost prefix usage, which affects query planning.

2

The order of columns in a composite index should match the most selective columns first to reduce search space effectively.

3

Covering composite indexes not only speed up queries but also reduce disk I/O by avoiding table lookups, which is critical in high-load systems.

When NOT to use

Composite indexes are not ideal when queries filter on columns out of order or only on non-leftmost columns. In such cases, single-column indexes or separate indexes might be better. Also, if the table is frequently updated, large composite indexes can slow down writes, so balance read and write needs carefully.

Production Patterns

In production, composite indexes are designed based on query patterns collected from slow query logs. Indexes often cover common WHERE and ORDER BY clauses together. Covering indexes are used for critical queries to minimize latency. Indexes are regularly reviewed and adjusted as application queries evolve.

Connections

Hashing

Alternative indexing method

Understanding composite indexes helps contrast them with hashing indexes, which excel at exact matches but do not support range queries or multi-column ordering.

Sorting algorithms

Underlying principle of ordered data

Knowing how composite indexes rely on sorted order connects to sorting algorithms, explaining why order matters for efficient searching.

Postal addressing systems

Hierarchical multi-part keys

Composite indexes work like postal addresses where order of parts (country, city, street) matters to find a location quickly, showing how hierarchical keys optimize search.

Common Pitfalls

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

Wrong approach:

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

Correct approach:

CREATE INDEX idx ON users (last_name, first_name); -- order matches query filters

Root cause:

Misunderstanding that the leftmost column in the index must match the first column 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 realizing that composite indexes require filtering on the leftmost column(s) to be used.

#3 Adding too many columns to a composite index unnecessarily.

Wrong approach:

CREATE INDEX idx ON sales (region, category, product, date, price); -- many columns not needed

Correct approach:

CREATE INDEX idx ON sales (region, category); -- only columns used in queries

Root cause:

Believing more columns always improve performance without considering index size and update cost.

Key Takeaways

Composite indexes speed up queries by indexing multiple columns together in a specific order.

The order of columns in a composite index is critical; queries must filter on the leftmost columns to benefit.

Composite indexes can also optimize sorting and can cover all query columns to avoid reading full table rows.

Misusing composite indexes by ignoring column order or adding unnecessary columns can hurt performance.

Designing composite indexes based on actual query patterns is essential for efficient and fast database operations.

Practice

1. What is the main purpose of a composite index in MySQL?

easy

A. To create a backup of the database automatically

B. To speed up queries that filter or sort by multiple columns together

C. To store data in multiple tables simultaneously

D. To encrypt data in multiple columns

Solution

1. Step 1: Understand what an index does

   An index helps MySQL find data faster by organizing it for quick lookup.

2. Step 2: Understand composite index purpose

   A composite index uses multiple columns together to speed up queries that filter or sort by those columns combined.

3. Final Answer:

   To speed up queries that filter or sort by multiple columns together -> Option B

4. Quick Check:

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

Hint: Composite indexes speed up multi-column filters and sorts [OK]

Common Mistakes:

• Thinking composite indexes store data
• Confusing indexes with backups
• Assuming encryption is related to indexes

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

easy

A. CREATE INDEX idx_name ON users (first_name, last_name);

B. CREATE INDEX idx_name ON users first_name, last_name;

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

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

Solution

1. Step 1: Recall correct CREATE INDEX syntax

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

2. Step 2: Match syntax with options

   CREATE INDEX idx_name ON users (first_name, last_name); matches the correct syntax with parentheses around columns and ON keyword before table name.

3. Final Answer:

   CREATE INDEX idx_name ON users (first_name, last_name); -> Option A

4. Quick Check:

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

Hint: Use parentheses around columns after ON table_name [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 people, which query will use the index efficiently?

medium

A. SELECT * FROM people WHERE city = 'Paris' AND age = 30;

B. SELECT * FROM people WHERE age = 30;

C. SELECT * FROM people WHERE age BETWEEN 20 AND 40;

D. SELECT * FROM people WHERE city LIKE '%Paris%' ORDER BY age DESC;

Solution

1. Step 1: Understand composite index column order

   The index is on (city, age), so queries filtering city first then age use it best.

2. Step 2: Analyze queries for index usage

   SELECT * FROM people WHERE city LIKE '%Paris%' ORDER BY age DESC;: LIKE '%Paris%' prevents index use due to leading wildcard. SELECT * FROM people WHERE age = 30;: filters only age (second column), skips prefix. SELECT * FROM people WHERE age BETWEEN 20 AND 40;: range on age only, cannot use prefix. SELECT * FROM people WHERE city = 'Paris' AND age = 30;: city then age equality matches prefix perfectly.

3. Final Answer:

   SELECT * FROM people WHERE city = 'Paris' AND age = 30; -> Option A

4. Quick Check:

   Filter starts with first index column = SELECT * FROM people WHERE city = 'Paris' AND age = 30; [OK]

Hint: Filter starting with first index column uses composite index best [OK]

Common Mistakes:

• Assuming any column filter uses composite index fully
• Ignoring column order in composite index
• Thinking ORDER BY always uses composite index

4. You created a composite index on (department, salary) but your query SELECT * FROM employees WHERE salary > 50000; is slow. What is the likely problem?

medium

A. The table has no data

B. The index syntax was incorrect

C. Composite indexes cannot be used with numeric columns

D. The query does not filter on the first column of the composite index

Solution

1. Step 1: Recall composite index usage rules

   Composite indexes work best when queries filter starting from the first indexed column.

2. Step 2: Analyze the query and index

   The index is on (department, salary), but query filters only salary (second column), so index is not used efficiently, causing slowness.

3. Final Answer:

   The query does not filter on the first column of the composite index -> Option D

4. Quick Check:

   Filter must start with first index column [OK]

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

Common Mistakes:

• Assuming any column filter uses composite index
• Blaming syntax without checking query
• Thinking numeric columns can't be indexed

5. You have a table orders with columns customer_id, order_date, and status. You often run this query:
SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped' ORDER BY order_date DESC;
Which composite index will optimize this query best?

hard

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

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

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

D. CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

Solution

1. Step 1: Identify query filter and order columns

   The query filters by customer_id and status, then orders by order_date descending.

2. Step 2: Choose index column order for best performance

   Index should start with columns used in WHERE filters in order: customer_id, then status, then order_date for ORDER BY.

3. Step 3: Match index options

   CREATE INDEX idx_customer_status_date ON orders (customer_id, status, order_date); matches this order exactly, so it optimizes filtering and ordering best.

4. Final Answer:

   CREATE INDEX idx_customer_status_date ON orders (customer_id, status, order_date); -> Option C

5. Quick Check:

   Index columns match WHERE then ORDER BY order [OK]

Hint: Index columns order: filter columns first, then order column [OK]

Common Mistakes:

• Putting ORDER BY column before filter columns
• Ignoring filter columns order
• Creating index missing important filter column