Composite indexes in MySQL - Step-by-Step Execution

Concept Flow - Composite indexes

Create Composite Index on (col1, col2)

↓

Insert/Update rows

↓

MySQL uses index to speed up queries

↓

Query with WHERE col1=? AND col2=?

↓

Index helps find rows faster

↓

Return matching rows

Composite indexes combine multiple columns to speed up queries that filter on those columns together.

Execution Sample

MySQL

CREATE INDEX idx_name ON table_name(col1, col2);
SELECT * FROM table_name WHERE col1 = 'A' AND col2 = 'B';

Create a composite index on col1 and col2, then query filtering on both columns uses the index.

Execution Table

Step	Action	Index Usage	Result
1	Create composite index on (col1, col2)	Index created	Index ready for use
2	Insert row ('A', 'B', ...)	Index updated with ('A','B')	Row inserted
3	Insert row ('A', 'C', ...)	Index updated with ('A','C')	Row inserted
4	Query WHERE col1='A' AND col2='B'	Index used to find ('A','B') quickly	Row with ('A','B') returned
5	Query WHERE col1='A'	Index used partially (prefix)	Rows with col1='A' returned
6	Query WHERE col2='B'	Index NOT used efficiently	Full scan or other index used
7	Query WHERE col1='A' AND col2='D'	Index used to find ('A','D')	No matching rows found
8	End of operations	-	-

💡 Queries stop after all matching rows are found or no matches exist.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	Final
Index Entries	empty	('A','B')	('A','B'), ('A','C')	Used to find ('A','B')	Used to find all with col1='A'	Not used efficiently	Used to find ('A','D')	Final state unchanged

Key Moments - 2 Insights

Why does the query with only col2='B' not use the composite index efficiently?

Because the composite index is ordered first by col1, then col2. Without filtering on col1 first, the index cannot be used efficiently as shown in step 6 of the execution_table.

How does the composite index help when querying with both col1 and col2?

The index stores values sorted by col1 then col2, so queries filtering on both can quickly locate matching rows, as shown in step 4.

Visual Quiz - 3 Questions

Look at the execution_table at step 5. What does the index help with?

A Finding rows with col1='A' quickly using the index prefix

B Finding rows with col2='B' quickly

C Full table scan

D No rows returned

💡 Hint

Check the 'Index Usage' column at step 5 in the execution_table.

At which step does the query NOT use the composite index efficiently?

A Step 4

B Step 2

C Step 6

D Step 7

💡 Hint

Look for 'Index NOT used efficiently' in the execution_table.

If we add a query filtering only on col2='B', what would happen to index usage?

A Index used fully

B Index not used efficiently, likely full scan

C Index used partially

D Query fails

💡 Hint

Refer to step 6 in the execution_table and variable_tracker.

Concept Snapshot

Composite Indexes:
- Created on multiple columns (e.g., col1, col2)
- Speeds up queries filtering on col1 and col2 together
- Index is ordered by first column, then second
- Queries filtering only on second column don't use index efficiently
- Useful for multi-column search conditions

Full Transcript

Composite indexes in MySQL combine two or more columns into one index. This helps speed up queries that filter on those columns together. The index is sorted first by the first column, then by the second. When you query filtering on both columns, MySQL can quickly find matching rows using the index. If you query filtering only on the first column, the index can still help by using the prefix. But if you filter only on the second column, the index is not used efficiently because it is ordered by the first column first. This means MySQL may do a full table scan or use another index. Creating composite indexes is useful when your queries often filter on multiple columns together.