Single column index in SQL - Step-by-Step Execution

Concept Flow - Single column index

Create Table

↓

Insert Data

↓

Create Single Column Index

↓

Query Uses Index

↓

Faster Data Retrieval

This flow shows creating a table, adding data, creating an index on one column, then querying that column to get faster results.

Execution Sample

SQL

CREATE TABLE employees (id INT, name VARCHAR(50));
INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
CREATE INDEX idx_name ON employees(name);
SELECT * FROM employees WHERE name = 'Bob';

Create a table, add three employees, create an index on the name column, then query by name to use the index.

Execution Table

Step	Action	Details	Result
1	Create Table	Table employees with columns id, name	Table created
2	Insert Data	Add 3 rows: Alice, Bob, Carol	3 rows inserted
3	Create Index	Create index idx_name on column name	Index idx_name created
4	Query	SELECT * FROM employees WHERE name = 'Bob'	Uses idx_name to find Bob quickly
5	Return Result	Row with name 'Bob'	(2, 'Bob')

💡 Query completes after using index to find matching row

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
employees table	empty	3 rows (Alice, Bob, Carol)	3 rows + index on name	3 rows + index used	3 rows + index used
idx_name	none	none	index on name created	index used for query	index used for query
query result	none	none	none	row with Bob found	row with Bob returned

Key Moments - 3 Insights

Why does creating an index on a single column speed up queries?

Because the index stores sorted pointers to rows by that column, the database can quickly find matching rows without scanning the whole table, as shown in execution_table step 4.

Does the index change the data in the table?

No, the index is a separate structure that points to the data. The table rows remain the same, as seen in variable_tracker where the employees table rows stay constant.

Will the query always use the index?

Usually yes for queries filtering on the indexed column, but the database optimizer decides. In this example, the query uses idx_name as shown in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the index created?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'Action' column for 'Create Index' in execution_table

According to variable_tracker, what is the state of the employees table after step 3?

A Empty table

B 3 rows with no index

C 3 rows with index on name

D Index created but no rows

💡 Hint

Look at the 'employees table' row under 'After Step 3' in variable_tracker

If we query a column without an index, how would the execution_table change?

A Query step would show index used

B Query step would show full table scan

C Index creation step would be missing

D Table creation step would fail

💡 Hint

Think about what happens when no index exists for a query column, check execution_table step 4

Concept Snapshot

Single column index:
CREATE INDEX index_name ON table(column);
Speeds up queries filtering on that column.
Index stores sorted pointers to rows.
Does not change table data.
Used automatically by queries on that column.

Full Transcript

This visual execution shows how a single column index works in a database. First, a table named employees is created with columns id and name. Then, three rows are inserted: Alice, Bob, and Carol. Next, an index named idx_name is created on the name column. When a query searches for name = 'Bob', the database uses the idx_name index to find the row quickly instead of scanning the whole table. The variable tracker shows the employees table has 3 rows throughout, and the index is created after step 3. Key moments clarify that the index speeds up queries by storing sorted pointers, does not change the table data, and is usually used automatically by the database optimizer. The quiz questions test understanding of when the index is created, the table state after index creation, and what happens if a query runs without an index. The concept snapshot summarizes the syntax and behavior of single column indexes.