Composite indexes in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a composite index in MySQL?

A composite index is an index on two or more columns in a table. It helps speed up queries that filter or sort by those columns together.

intermediate

Why use a composite index instead of multiple single-column indexes?

A composite index is more efficient for queries that use multiple columns together because it stores combined values, reducing the need to merge separate indexes.

intermediate

How does the order of columns in a composite index affect query performance?

The order matters because MySQL uses the leftmost prefix of the index. Queries filtering on the first columns in order can use the index efficiently, but filtering only on later columns may not use it.

beginner

Example: CREATE INDEX idx_name_age ON users(name, age); What does this do?

It creates a composite index on the 'name' and 'age' columns of the 'users' table. Queries filtering by name or by both name and age can use this index.

intermediate

Can a composite index speed up queries filtering only by the second column?

No, because MySQL uses the leftmost prefix rule. The index can speed up queries filtering by the first column or the first and second columns together, but not by the second column alone.

What does a composite index in MySQL consist of?

A An index on multiple columns combined

B An index on a single column only

C A temporary index created during queries

D An index that stores only unique values

A composite index combines two or more columns into one index to speed up queries using those columns together.

Which query can use the composite index on (name, age)?

A WHERE age = 30

B WHERE name = 'Alice' AND age = 30

C WHERE city = 'NYC'

D WHERE age > 25

The composite index on (name, age) can be used when filtering by name alone or by both name and age, but not by age alone.

Why is the order of columns important in a composite index?

A Because the order does not affect performance

B Because the last column is always ignored

C Because columns are sorted alphabetically in the index

D Because MySQL uses the leftmost columns first in the index

MySQL uses the leftmost prefix of the composite index, so queries filtering on those columns can use the index efficiently.

What happens if you create separate indexes on 'name' and 'age' instead of a composite index?

A Queries filtering by both columns may be slower than with a composite index

B Queries filtering by both columns will always be faster

C MySQL merges the indexes automatically with no difference

D Indexes on single columns cannot be created

Separate indexes may require MySQL to merge results, which can be slower than using a composite index for queries filtering on both columns.

Which of these is a valid reason to use a composite index?

A To make all queries slower

B To reduce the size of the database

C To speed up queries filtering on multiple columns together

D To avoid creating any indexes on the table

Composite indexes improve performance for queries that filter or sort by multiple columns together.

Explain what a composite index is and why the order of columns matters.

Think about how MySQL uses the first columns in the index for filtering.

Describe a scenario where a composite index improves query performance compared to single-column indexes.

Imagine searching a phone book by last name and first name together.