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.

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