Composite index and column order in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of column order in composite index on query performance

Consider a table Orders with columns customer_id and order_date. A composite index is created as (customer_id, order_date). Which query will benefit most from this index?

A SELECT * FROM Orders WHERE order_date = '2024-01-01';

B SELECT * FROM Orders WHERE order_date = '2024-01-01' AND customer_id = 123;

C SELECT * FROM Orders WHERE customer_id = 123 AND order_date = '2024-01-01';

D SELECT * FROM Orders WHERE customer_id = 123;

💡 Hint

Think about how the index is structured and which columns are used first in the WHERE clause.

The composite index (customer_id, order_date) is ordered first by customer_id, then by order_date. Queries filtering on customer_id and optionally order_date can use the index efficiently. Queries B and C both filter on both columns, so they benefit most. The order of conditions in the WHERE clause does not affect index usage. Query A filters only on order_date, which is the second column, so the index is not used efficiently. Query D filters only on customer_id, which can use the index but less specifically than B and C.

🧠 Conceptual

intermediate

Understanding composite index column order

Why does the order of columns in a composite index matter?

A Because the database uses the first column to quickly narrow down rows before checking the second column.

B Because the database always searches columns in alphabetical order regardless of index order.

C Because the order determines which columns can be NULL in the index.

D Because the order affects the physical storage location of the table rows.

💡 Hint

Think about how indexes help the database find data quickly.

The order of columns in a composite index matters because the database uses the first column to filter rows first, then uses the second column to further narrow down results. This makes searching efficient. The other options are incorrect because alphabetical order is not used, NULL handling is unrelated to order, and physical storage of rows is not affected by index column order.

📝 Syntax

advanced

Correct syntax to create a composite index

Which of the following SQL statements correctly creates a composite index on columns last_name and first_name in a table Employees?

A CREATE INDEX idx_name ON Employees last_name, first_name;

B CREATE INDEX idx_name ON Employees (last_name, first_name);

C CREATE INDEX idx_name (last_name, first_name) ON Employees;

D CREATE INDEX idx_name ON Employees WHERE last_name, first_name;

💡 Hint

Remember the order of clauses in the CREATE INDEX statement.

The correct syntax is CREATE INDEX index_name ON table_name (column1, column2);. Option B follows this syntax. Options A, C, and D have syntax errors such as missing parentheses or incorrect clause order.

❓ optimization

advanced

Choosing column order for composite index to optimize queries

You have a table Sales with columns region, sales_rep, and sale_date. Most queries filter by sales_rep and then by region. Which composite index column order is best for performance?

A (region, sale_date)

B (region, sales_rep)

C (sales_rep, region)

D (sale_date, sales_rep)

💡 Hint

Think about which column is filtered first in queries.

Since queries filter first by sales_rep and then by region, the composite index should start with sales_rep to efficiently narrow down rows, followed by region. Option C matches this order. Other options start with columns filtered later or not filtered at all, reducing index effectiveness.

🔧 Debug

expert

Diagnose why a composite index is not used

A composite index exists on (category, price) in a Products table. The query SELECT * FROM Products WHERE price > 100; is slow and does not use the index. Why?

A Because the query filters only on the second column of the composite index, the index cannot be used efficiently.

B Because the index must be rebuilt before it can be used for queries.

C Because the query uses a greater than operator which is not supported by indexes.

D Because the table has no primary key defined.

💡 Hint

Think about how composite indexes work with leading columns.

Composite indexes work best when the query filters on the first column or a leftmost prefix of the index. Since the query filters only on price, which is the second column, the index on (category, price) cannot be used efficiently. The other options are incorrect because indexes support range queries, rebuilding is not required for usage, and primary key presence does not affect index usage here.