Composite index and column order in SQL - Step-by-Step Execution

Concept Flow - Composite index and column order

Create Composite Index on (A, B)

↓

Query uses WHERE A = ? AND B = ?

Yes→Index fully used

No↓

Query uses WHERE A = ? only

→Index partially used

↓

Query uses WHERE B = ? only

→Index NOT used efficiently

↓

Query uses WHERE B = ? AND A = ?

→Order matters: same as index order

↓

Performance varies

The order of columns in a composite index affects how queries can use it efficiently. Queries filtering on the first column can use the index fully or partially; filtering only on later columns is less efficient.

Execution Sample

SQL

CREATE INDEX idx_ab ON table_name (A, B);

SELECT * FROM table_name WHERE A = 10 AND B = 20;
SELECT * FROM table_name WHERE A = 10;
SELECT * FROM table_name WHERE B = 20;

Shows how a composite index on columns A and B is used by queries filtering on A and B, A only, or B only.

Execution Table

Step	Query Condition	Index Usage	Explanation
1	WHERE A = 10 AND B = 20	Full index used	Both columns match index order, so index is fully used for fast lookup
2	WHERE A = 10	Partial index used	Only first column used, index can still speed up search but less selective
3	WHERE B = 20	Index not used efficiently	Index starts with A, so filtering only on B cannot use index effectively
4	WHERE B = 20 AND A = 10	Full index used	Order of conditions in query does not matter, index order matters
5	End	-	No more queries to evaluate

💡 All queries evaluated for index usage based on composite index column order

Variable Tracker

Variable	Start	After Query 1	After Query 2	After Query 3	After Query 4	Final
Index Usage	None	Full index used	Partial index used	Index not used efficiently	Full index used	Full index used or partial depending on query

Key Moments - 2 Insights

Why does filtering only on column B not use the composite index efficiently?

Because the composite index starts with column A, the database cannot use the index to filter only by B without first filtering by A, as shown in execution_table row 3.

Does the order of conditions in the WHERE clause affect index usage?

No, the order of conditions in the query does not matter; what matters is the order of columns in the index, as shown in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the index usage when the query filters on A = 10 only?

A Full index used

B Index not used

C Partial index used

D Index used only on B

💡 Hint

Check execution_table row 2 under 'Index Usage'

At which step does filtering only on B = 20 occur and how is the index used?

A Step 3, index fully used

B Step 3, index not used efficiently

C Step 2, partial index used

D Step 4, full index used

💡 Hint

Look at execution_table row 3 for filtering on B only

If the composite index was created as (B, A) instead of (A, B), which query would benefit most?

A WHERE B = 20

B WHERE A = 10 AND B = 20

C WHERE A = 10

D None of the above

💡 Hint

Think about which column is first in the index and check variable_tracker for index usage

Concept Snapshot

Composite indexes are built on multiple columns in a specific order.
Queries filtering on the first column can use the index fully or partially.
Filtering only on later columns is inefficient.
Order of columns in the index matters more than order of WHERE conditions.
Design indexes based on common query filters for best performance.

Full Transcript

This visual execution shows how a composite index on columns A and B affects query performance. The index is created with columns in order (A, B). Queries filtering on both A and B use the index fully for fast lookups. Queries filtering only on A use the index partially, still gaining some speed. Queries filtering only on B cannot use the index efficiently because the index starts with A. The order of conditions in the WHERE clause does not affect index usage; the index column order is what matters. Understanding this helps design better indexes and write queries that run faster.

Practice

1. What is the main benefit of using a composite index in a database?

easy

A. It encrypts the data for security.

B. It reduces the size of the database file.

C. It automatically backs up the database.

D. It speeds up queries filtering on multiple columns together.

Solution

1. Step 1: Understand what a composite index does

   A composite index is created on multiple columns to help the database find rows faster when queries filter on those columns together.

2. Step 2: Compare options to the definition

   Only It speeds up queries filtering on multiple columns together. correctly describes the benefit of speeding up queries using multiple columns. Other options describe unrelated features.

3. Final Answer:

   It speeds up queries filtering on multiple columns together. -> Option D

4. Quick Check:

   Composite index = speeds up multi-column queries [OK]

Hint: Composite index helps multi-column filters run faster [OK]

Common Mistakes:

• Thinking it reduces database size
• Confusing indexing with backup or encryption
• Assuming it speeds up single-column queries only

2. Which of the following is the correct syntax to create a composite index on columns last_name and first_name in a table employees?

easy

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;

Solution

1. Step 1: Recall correct CREATE INDEX syntax

   The correct syntax is: CREATE INDEX index_name ON table_name (column1, column2);

2. Step 2: Match options to syntax

   CREATE INDEX idx_name ON employees (last_name, first_name); matches the correct syntax exactly. Options A, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   CREATE INDEX idx_name ON employees (last_name, first_name); -> Option B

4. Quick Check:

   CREATE INDEX ... ON table (columns) [OK]

Hint: Use parentheses around columns after ON table [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 users, which query will use the index efficiently?

medium

A. SELECT * FROM users WHERE age = 25 AND city = 'Boston';

B. SELECT * FROM users WHERE age > 30;

C. SELECT * FROM users WHERE city = 'Boston' AND age > 30;

D. SELECT * FROM users WHERE city = 'Boston' ORDER BY age DESC;

Solution

1. Step 1: Understand index column order usage

   A composite index on (city, age) can be used efficiently if the query filters on city first, then age.

2. Step 2: Analyze each query

   SELECT * FROM users WHERE city = 'Boston' AND age > 30; filters on city and age, matching index order. SELECT * FROM users WHERE age > 30; filters only on age (second column), so index is less useful. SELECT * FROM users WHERE age = 25 AND city = 'Boston'; filters age first, then city, which is less efficient. SELECT * FROM users WHERE city = 'Boston' ORDER BY age DESC; filters city but only orders by age, which may use index partially.

3. Final Answer:

   SELECT * FROM users WHERE city = 'Boston' AND age > 30; -> Option C

4. Quick Check:

   Index (city, age) best for city then age filters [OK]

Hint: Filter on first index column before others [OK]

Common Mistakes:

• Assuming index works well if filtering only second column
• Ignoring column order in composite index
• Thinking ORDER BY always uses index fully

4. You created a composite index on (department, salary) but your query filtering only on salary is slow. What is the likely cause?

medium

A. The index column order starts with department, so filtering only salary can't use it well.

B. The index is corrupted and needs rebuilding.

C. The salary column data type is incompatible with indexing.

D. The database does not support composite indexes.

Solution

1. Step 1: Recall how composite index column order affects usage

   Composite indexes work best when filtering starts with the first column. Filtering only on the second column (salary) won't use the index efficiently.

2. Step 2: Evaluate options

   The index column order starts with department, so filtering only salary can't use it well. correctly explains the cause. Options A, B, and D are unlikely or incorrect because the index exists and salary can be indexed normally.

3. Final Answer:

   The index column order starts with department, so filtering only salary can't use it well. -> Option A

4. Quick Check:

   Filter must start with first index column [OK]

Hint: Filter on first index column to use composite index [OK]

Common Mistakes:

• Assuming any column in index can be filtered efficiently alone
• Blaming index corruption without checking usage
• Ignoring column order importance

5. You have a table orders with columns customer_id, order_date, and status. You often run queries filtering by customer_id and sorting by order_date. Which composite index will optimize these queries best?

hard

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

B. CREATE INDEX idx_date_customer ON orders (order_date, customer_id);

C. CREATE INDEX idx_status_customer ON orders (status, customer_id);

D. CREATE INDEX idx_order ON orders (order_date);

Solution

1. Step 1: Identify query filter and sort columns

   Queries filter by customer_id and sort by order_date, so index should start with customer_id and include order_date next.

2. Step 2: Match index column order to query pattern

   CREATE INDEX idx_customer_date ON orders (customer_id, order_date); matches this order (customer_id, order_date), allowing efficient filtering and sorting. CREATE INDEX idx_date_customer ON orders (order_date, customer_id); reverses order, less efficient for filtering. Options A, B, and C do not match query needs.

3. Final Answer:

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

4. Quick Check:

   Index columns match filter then sort order [OK]

Hint: Index columns in filter then sort order [OK]

Common Mistakes:

• Reversing column order in composite index
• Indexing unrelated columns
• Using single-column index when composite needed