Covering index concept in SQL - Step-by-Step Execution

Concept Flow - Covering index concept

Query issued

↓

Check index for needed columns

↓

Index has all columns?

No→Fetch missing columns from table

Yes↓

Return data directly from index

↓

Query result delivered

When a query runs, the database checks if an index contains all columns needed. If yes, it uses the index alone to answer, skipping the main table.

Execution Sample

SQL

CREATE INDEX idx_name ON employees(last_name, first_name);
SELECT last_name, first_name FROM employees WHERE last_name = 'Smith';

This query uses an index on last_name and first_name to get data without reading the full table.

Execution Table

Step	Action	Index Columns Checked	Table Accessed	Result
1	Query starts	-	No	Waiting for plan
2	Check index idx_name	last_name, first_name	No	Index covers needed columns
3	Use index to find last_name='Smith'	last_name, first_name	No	Found matching rows in index
4	Return last_name, first_name from index	last_name, first_name	No	Data returned without table access
5	Query ends	-	No	Result delivered from index

💡 Index contains all requested columns, so no table access needed; query completes.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Index Used	No	Yes	Yes	Yes	Yes
Table Accessed	No	No	No	No	No
Rows Found	0	0	Multiple	Multiple	Multiple

Key Moments - 2 Insights

Why does the query not access the main table?

Because the index contains all columns needed by the query, as shown in execution_table step 2 and 4, so the database can answer directly from the index.

What happens if the index does not have all requested columns?

The database must access the main table to get missing columns, which is slower. This is shown by the 'No' branch in the concept_flow diagram.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the database confirm the index covers all needed columns?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check the 'Index Columns Checked' and 'Result' columns in execution_table row for Step 2.

According to variable_tracker, does the table get accessed during query execution?

A Yes, after Step 3

B Yes, after Step 4

C No, table is never accessed

D Only at the start

💡 Hint

Look at 'Table Accessed' row across all steps in variable_tracker.

If the query requested an additional column not in the index, what would change in the execution_table?

A Step 3 would access the table to fetch missing columns

B Step 4 would return data only from index

C Step 2 would say index covers all columns

D Query would end at Step 1

💡 Hint

Refer to concept_flow where missing columns cause table access after index check.

Concept Snapshot

Covering index means an index has all columns a query needs.
If yes, database reads only the index, skipping the table.
This speeds up queries by reducing data reads.
Create indexes with all needed columns for frequent queries.
If missing columns, table access slows query down.

Full Transcript

When a database query runs, it first checks if an index contains all the columns the query needs. If the index covers all columns, the database can return results directly from the index without reading the main table. This is called a covering index. For example, if an index has last_name and first_name, and the query requests only those columns, the database uses the index alone. This speeds up the query because reading the index is faster than reading the full table. If the index lacks some columns, the database must access the table to get missing data, which is slower. The execution table shows each step: starting the query, checking the index, using the index to find rows, returning data from the index, and finishing the query. The variable tracker shows that the table is never accessed because the index covers all needed columns. Understanding covering indexes helps write faster queries by designing indexes that include all columns a query needs.