Index impact on INSERT and UPDATE in SQL - Step-by-Step Execution

Concept Flow - Index impact on INSERT and UPDATE

Start INSERT/UPDATE

↓

Check for Indexes on Table

↓

If Index Exists?

No→Insert/Update Data Directly

Yes↓

Insert/Update Data in Table

↓

Update All Relevant Indexes

↓

Finish Operation

When inserting or updating data, the database checks if indexes exist. If yes, it updates those indexes after changing the data, which adds extra steps and time.

Execution Sample

SQL

INSERT INTO employees (id, name) VALUES (1, 'Alice');
UPDATE employees SET name = 'Alicia' WHERE id = 1;

Insert a new employee and then update the employee's name, showing how indexes affect these operations.

Execution Table

Step	Operation	Index Present?	Action	Result
1	Start INSERT	Yes	Insert row into table	Row inserted
2	Update Indexes	Yes	Update index entries for new row	Indexes updated
3	Finish INSERT	Yes	Complete operation	Insert done
4	Start UPDATE	Yes	Find row by index	Row found
5	Update row data	Yes	Change name field	Data updated
6	Update Indexes	Yes	Update index entries for changed data	Indexes updated
7	Finish UPDATE	Yes	Complete operation	Update done
8	Start INSERT	No	Insert row into table	Row inserted
9	Finish INSERT	No	Complete operation	Insert done
10	Start UPDATE	No	Find row by scan	Row found
11	Update row data	No	Change name field	Data updated
12	Finish UPDATE	No	Complete operation	Update done

💡 Operations finish after data and indexes are updated (if indexes exist), or just data if no indexes.

Variable Tracker

Variable	Start	After Insert	After Update	Final
Table Rows	0	1	1	1
Index Entries	0	1	1	1
Data Field 'name'	N/A	'Alice'	'Alicia'	'Alicia'

Key Moments - 3 Insights

Why does updating a row take longer when indexes exist?

Because after changing the data, the database must also update all related index entries, as shown in execution_table rows 5 and 6.

What happens if there are no indexes during an INSERT?

The database inserts the row directly without extra steps for indexes, as seen in execution_table rows 8 and 9.

How does the database find the row to update when indexes exist?

It uses the index to quickly locate the row, shown in execution_table row 4, instead of scanning the whole table.

Visual Quiz - 3 Questions

Look at the execution_table, what action happens at step 6?

A Update index entries for changed data

B Insert row into table

C Complete operation

D Find row by scan

💡 Hint

Check the 'Action' column at step 6 in execution_table.

At which step does the database finish the UPDATE operation when indexes exist?

A Step 5

B Step 6

C Step 7

D Step 8

💡 Hint

Look for 'Finish UPDATE' in the 'Operation' column with 'Yes' in 'Index Present?'

If the table has no indexes, how does the database find the row to update?

A Using the index

B By scanning the whole table

C It cannot find the row

D Using a cache

💡 Hint

See execution_table row 10 under 'Action' for no index case.

Concept Snapshot

Index impact on INSERT and UPDATE:
- When inserting or updating, indexes must be updated if present.
- This adds extra steps and time to the operation.
- Without indexes, data changes happen directly.
- Indexes speed up finding rows but slow down writes.
- Balance indexes for read vs write performance.

Full Transcript

When you insert or update data in a table, the database first checks if there are any indexes on that table. If indexes exist, after inserting or updating the data, the database must also update the index entries to keep them accurate. This means more steps and more time are needed compared to tables without indexes. For example, when inserting a new employee, the row is added to the table, then the indexes are updated. When updating the employee's name, the database uses the index to find the row quickly, updates the data, then updates the indexes again. Without indexes, the database inserts or updates data directly and finds rows by scanning the table, which can be slower for large tables but faster for writes. This shows the trade-off: indexes speed up reading but slow down inserting and updating.