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.

Practice

1.

What is the main purpose of creating a single column index in a database?

easy

A. To store duplicate values in the column

B. To automatically backup the database

C. To prevent any changes to the column data

D. To speed up queries that filter or sort by that column

Solution

1. Step 1: Understand what an index does

   An index helps the database find rows faster when searching or sorting by the indexed column.

2. Step 2: Identify the purpose of a single column index

   A single column index speeds up queries that use that column in WHERE or ORDER BY clauses.

3. Final Answer:

   To speed up queries that filter or sort by that column -> Option D

4. Quick Check:

   Index speeds up queries = B [OK]

Hint: Index speeds up searches on one column [OK]

Common Mistakes:

• Thinking index stores duplicate values
• Believing index prevents data changes
• Confusing index with backup

2.

Which of the following is the correct syntax to create a single column index on the email column of a table named users?

easy

A. CREATE INDEX users ON idx_email(email);

B. CREATE INDEX idx_email ON users(email);

C. CREATE INDEX email ON users idx_email;

D. CREATE users INDEX idx_email(email);

Solution

1. Step 1: Recall the syntax for creating an index

   The correct syntax is: CREATE INDEX index_name ON table_name(column_name);

2. Step 2: Match the syntax with the options

   CREATE INDEX idx_email ON users(email); matches the correct syntax exactly.

3. Final Answer:

   CREATE INDEX idx_email ON users(email); -> Option B

4. Quick Check:

   CREATE INDEX index_name ON table(column) = A [OK]

Hint: Syntax: CREATE INDEX name ON table(column) [OK]

Common Mistakes:

• Swapping table and index names
• Missing ON keyword
• Incorrect order of keywords

3.

Given a table products with columns id, name, and category, and a single column index on category, what will the query below most likely do?

SELECT * FROM products WHERE category = 'Books';

medium

A. Use the index to quickly find rows where category is 'Books'

B. Sort the results by category before returning

C. Return an error because index cannot be used in WHERE clause

D. Scan the entire table without using the index

Solution

1. Step 1: Understand how an index affects a WHERE query

   An index on the category column helps the database quickly locate rows matching the condition category = 'Books'.

2. Step 2: Analyze the query behavior with the index

   The query will use the index to avoid scanning the whole table, speeding up the search.

3. Final Answer:

   Use the index to quickly find rows where category is 'Books' -> Option A

4. Quick Check:

   Index speeds WHERE search = D [OK]

Hint: WHERE on indexed column uses index for speed [OK]

Common Mistakes:

• Assuming index is ignored in WHERE clause
• Thinking index causes errors in queries
• Confusing index use with sorting

4.

Consider this SQL statement to create an index:

CREATE INDEX idx_price ON products price;

What is wrong with this statement?

medium

A. Table name should come after the column name

B. Index name cannot start with 'idx_'

C. Missing parentheses around the column name

D. CREATE INDEX cannot be used on numeric columns

Solution

1. Step 1: Check the syntax for CREATE INDEX

   The column name must be inside parentheses after the table name.

2. Step 2: Identify the syntax error

   The statement is missing parentheses around price. It should be ON products(price).

3. Final Answer:

   Missing parentheses around the column name -> Option C

4. Quick Check:

   Column name needs parentheses = C [OK]

Hint: Column name must be in parentheses after table [OK]

Common Mistakes:

• Omitting parentheses around column
• Misplacing table and column names
• Thinking index names have restrictions

5.

You have a table orders with columns order_id, customer_id, and order_date. You want to speed up queries that filter by customer_id and sort by order_date. Which approach is best?

hard

A. Create a composite index on customer_id and order_date

B. Create a single column index on order_date only

C. Create a single column index on customer_id only

D. Create no index to avoid slowing down inserts

Solution

1. Step 1: Understand query needs

   Queries filter by customer_id and sort by order_date, so both columns matter.

2. Step 2: Choose the best index type

   A composite index on both columns helps filter and sort efficiently, better than single column indexes.

3. Final Answer:

   Create a composite index on customer_id and order_date -> Option A

4. Quick Check:

   Composite index for filter + sort = A [OK]

Hint: Use composite index for multiple column queries [OK]

Common Mistakes:

• Using single column index when composite is better
• Avoiding indexes fearing insert slowdown
• Indexing only sort column ignoring filter