Composite indexes in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of composite index on query filtering

Given a table orders with columns customer_id, order_date, and status, and a composite index on (customer_id, order_date), which query will most efficiently use the index?

A SELECT * FROM orders WHERE order_date = '2024-01-01' AND status = 'shipped';

B SELECT * FROM orders WHERE order_date = '2024-01-01';

C SELECT * FROM orders WHERE status = 'shipped' AND customer_id = 123;

D SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';

💡 Hint

Think about the order of columns in the composite index and which columns are filtered in the query.

The composite index on (customer_id, order_date) is most effective when the query filters on customer_id first, then order_date. Option D filters on both in the correct order, so it uses the index efficiently. Options A and D filter only on order_date, which is the second column, so the index is not fully used. Option D filters on status, which is not part of the index.

🧠 Conceptual

intermediate

Understanding leftmost prefix rule in composite indexes

Which of the following statements best describes the leftmost prefix rule for composite indexes?

A A composite index can only be used if the query filters on the first column of the index or a leftmost subset of columns in order.

B A composite index is only used if the query filters on the last column of the index.

C A composite index can be used by queries filtering on any combination of columns in the index, regardless of order.

D A composite index is ignored if the query filters on all columns in the index.

💡 Hint

Think about how the database uses the index starting from the first column.

The leftmost prefix rule means the database can use a composite index only if the query filters on the first column or a continuous sequence of columns starting from the first. For example, if the index is on (A, B, C), queries filtering on A or A and B or A, B, and C can use the index. Queries filtering only on B or C cannot.

📝 Syntax

advanced

Creating a composite index in MySQL

Which of the following SQL statements correctly creates a composite index named idx_customer_date on the customer_id and order_date columns of the orders table?

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

B CREATE COMPOSITE INDEX idx_customer_date ON orders (customer_id, order_date);

C CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

D CREATE INDEX idx_customer_date ON orders customer_id, order_date;

💡 Hint

Remember the correct syntax for creating indexes in MySQL.

Option C uses the correct syntax: CREATE INDEX index_name ON table_name (column1, column2); Option C reverses the column order, which is valid syntax but creates a different index. Option C uses a non-existent keyword COMPOSITE. Option C is missing parentheses around columns.

❓ optimization

advanced

Choosing the best composite index for query performance

You have a table sales with columns region, product_id, and sale_date. You often run this query:

SELECT * FROM sales WHERE product_id = 101 AND region = 'North';

Which composite index will optimize this query best?

A CREATE INDEX idx_region_product ON sales (region, product_id);

B CREATE INDEX idx_product_region ON sales (product_id, region);

C CREATE INDEX idx_sale_date_product ON sales (sale_date, product_id);

D CREATE INDEX idx_region_sale_date ON sales (region, sale_date);

💡 Hint

Consider which column is filtered first in the query and the order of columns in the index.

The query filters on product_id and region, but the order matters. Since the query filters on product_id and region, the index should start with product_id. Option B puts product_id first, which matches the query's filtering columns order. Options C and D do not include both columns used in the query. Option B puts region first, which is less optimal for this query.

🔧 Debug

expert

Diagnosing why a composite index is not used

You created a composite index on (category, price) in a products table. The query below does not use the index:

SELECT * FROM products WHERE price > 100;

Why is the composite index not used?

A Because the query filters only on the second column <code>price</code>, not the first column <code>category</code> of the composite index.

B Because the query uses a greater than operator, which is not supported by composite indexes.

C Because the index was created with the wrong column order; it should be (price, category).

D Because composite indexes cannot be used for range queries.

💡 Hint

Recall the leftmost prefix rule for composite indexes.

The composite index on (category, price) can only be used if the query filters on category first. Since the query filters only on price, the index is not used. The greater than operator is supported, and composite indexes can be used for range queries if the leftmost column is filtered. Changing column order might help but is not the direct reason here.

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