When indexes help and when they hurt in SQL - Step-by-Step Execution

Concept Flow - When indexes help and when they hurt

Query arrives

↓

Check for index on columns used

↓

Use index

↓

Fast search

↓

Return results

↓

Consider index maintenance cost on writes

When a query runs, the database checks if an index can speed up searching. Using an index helps read speed but can slow down writes because indexes need updating.

Execution Sample

SQL

SELECT * FROM employees WHERE department_id = 5;
-- Assume index on department_id exists

SELECT * FROM employees WHERE last_name LIKE '%son';
-- No index used here

Two queries: one uses an index for fast lookup, the other does not and scans the whole table.

Execution Table

Step	Query	Index Used?	Action	Performance Impact
1	SELECT * FROM employees WHERE department_id = 5;	Yes	Use index to find matching rows	Fast read
2	Return matching rows	N/A	Fetch rows by row IDs from index	Fast response
3	SELECT * FROM employees WHERE last_name LIKE '%son';	No	Full table scan to check each row	Slow read
4	Return matching rows	N/A	Return rows after scanning all	Slow response
5	INSERT INTO employees ...	Yes	Update table and index	Slower write due to index maintenance
6	UPDATE employees SET ...	Yes	Update table and index	Slower write due to index maintenance
7	DELETE FROM employees WHERE ...	Yes	Remove from table and index	Slower write due to index maintenance

💡 Queries stop after returning results; writes include extra index update steps causing slower performance.

Variable Tracker

Operation	Index Usage	Rows Scanned	Rows Returned	Write Cost
SELECT with index	Yes	Few (only matching)	Few	None
SELECT without index	No	All rows	Few	None
INSERT/UPDATE/DELETE	Yes	N/A	N/A	High (index updated)

Key Moments - 3 Insights

Why does using an index slow down INSERT or UPDATE operations?

Because each write must update the index structure as well as the table, adding extra work and time (see execution_table rows 5-7).

Why does a query with a condition like last_name LIKE '%son' not use an index?

Because the wildcard at the start prevents the database from using the index efficiently, so it scans the whole table (see execution_table row 3).

When does using an index help the most?

When the query filters on columns with an index and the condition allows the index to quickly find matching rows (see execution_table rows 1-2).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the database perform a full table scan?

A Step 3

B Step 1

C Step 5

D Step 2

💡 Hint

Check the 'Action' column for 'Full table scan' in execution_table row 3.

According to variable_tracker, which operation has the highest write cost?

A SELECT with index

B INSERT/UPDATE/DELETE

C SELECT without index

D SELECT with no condition

💡 Hint

Look at the 'Write Cost' column in variable_tracker row for INSERT/UPDATE/DELETE.

If the query changes to WHERE last_name LIKE 'son%', how would the execution_table change?

A Query would fail due to syntax error

B Still no index used, full table scan happens

C Index would be used, making the search faster

D Index would slow down the query

💡 Hint

Queries with wildcard only at the end can use indexes efficiently (see key_moments about LIKE patterns).

Concept Snapshot

When indexes help and when they hurt:
- Indexes speed up reads by quickly locating rows.
- Indexes slow down writes because they must be updated.
- Queries with conditions matching indexed columns benefit most.
- Wildcards at the start of LIKE prevent index use.
- Balance index use based on read/write patterns.

Full Transcript

This visual execution shows how indexes affect query performance. When a query filters on an indexed column, the database uses the index to quickly find matching rows, speeding up reads. However, if the query condition prevents index use, like a wildcard at the start of a LIKE pattern, the database scans the entire table, which is slower. Also, when inserting, updating, or deleting rows, the database must update the indexes, which slows down write operations. Understanding when indexes help or hurt helps design better databases and queries.