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.