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.