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.

Practice

1. What is the main impact of having many indexes on a table when performing INSERT operations?

easy

A. Inserts become slower because each index must be updated.

B. Inserts become faster because indexes help locate where to insert.

C. Inserts are unaffected by indexes.

D. Inserts cause indexes to be deleted automatically.

Solution

1. Step 1: Understand how indexes affect inserts

   When inserting a new row, the database must update all indexes related to that table to keep them accurate.

2. Step 2: Analyze the effect on performance

   Updating multiple indexes adds overhead, making inserts slower as more indexes exist.

3. Final Answer:

   Inserts become slower because each index must be updated. -> Option A

4. Quick Check:

   More indexes = slower inserts [OK]

Hint: More indexes slow inserts due to extra updates [OK]

Common Mistakes:

• Thinking indexes speed up inserts
• Believing inserts ignore indexes
• Assuming indexes get deleted on insert

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

easy

A. CREATE INDEX idx_email ON users(email);

B. CREATE users INDEX ON email;

C. INDEX CREATE ON users(email);

D. CREATE INDEX users_email email;

Solution

1. Step 1: Recall standard SQL index creation syntax

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

2. Step 2: Match the syntax to options

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

3. Final Answer:

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

4. Quick Check:

   CREATE INDEX ... ON table(column) [OK]

Hint: Use CREATE INDEX index_name ON table(column) [OK]

Common Mistakes:

• Swapping table and column names
• Omitting ON keyword
• Incorrect order of keywords

3. Given a table products with a non-clustered index on category_id, what happens internally when you run this query?

UPDATE products SET price = price * 1.1 WHERE category_id = 5;

medium

A. The update ignores the index and scans the whole table.

B. The update is slower because the index on category_id must be rebuilt entirely.

C. The update fails because indexes cannot be used with UPDATE statements.

D. The update is faster because the index helps find rows quickly, but the index must be updated if category_id changes.

Solution

1. Step 1: Understand index use in WHERE clause

   The index on category_id helps quickly find rows where category_id = 5.

2. Step 2: Consider index updates during UPDATE

   Since category_id is not changed, the index entries remain valid; only the data column price is updated.

3. Final Answer:

   The update is faster because the index helps find rows quickly, but the index must be updated if category_id changes. -> Option D

4. Quick Check:

   Index speeds search; update affects data, not index keys [OK]

Hint: Indexes speed WHERE search; update index only if key changes [OK]

Common Mistakes:

• Thinking index rebuilds fully on update
• Assuming update ignores indexes
• Believing update fails with indexes

4. You have a table orders with an index on order_date. You run this query:

UPDATE orders SET order_date = '2024-01-01' WHERE order_id = 100;

But the update is very slow. What is a likely cause?

medium

A. Indexes do not affect update speed, so the cause is elsewhere.

B. The query syntax is incorrect, causing a full table scan.

C. The index on order_date must be updated because the indexed column value changed.

D. The order_id column is not indexed, causing slow search.

Solution

1. Step 1: Identify which columns are indexed and updated

   The order_date column is indexed and is being updated.

2. Step 2: Understand index maintenance on update

   Updating an indexed column requires updating the index entry, which adds overhead and slows the update.

3. Final Answer:

   The index on order_date must be updated because the indexed column value changed. -> Option C

4. Quick Check:

   Updating indexed columns slows updates due to index maintenance [OK]

Hint: Updating indexed columns slows updates due to index changes [OK]

Common Mistakes:

• Ignoring index update cost
• Blaming syntax errors without checking
• Assuming indexes don't affect update speed

5. You manage a large table employees with indexes on department_id and last_name. You notice INSERT and UPDATE operations are slow. Which strategy best balances read speed and write performance?

hard

A. Remove all indexes to speed up writes, sacrificing read speed.

B. Keep only essential indexes used frequently in queries, reducing overhead on writes.

C. Add more indexes to speed up all queries regardless of write cost.

D. Disable indexes during inserts and updates and rebuild them after every operation.

Solution

1. Step 1: Understand the trade-off between indexes and write speed

   More indexes improve read speed but slow down inserts and updates because each index must be maintained.

2. Step 2: Choose a balanced approach

   Keeping only essential indexes used often reduces write overhead while maintaining reasonable read performance.

3. Final Answer:

   Keep only essential indexes used frequently in queries, reducing overhead on writes. -> Option B

4. Quick Check:

   Balance indexes for reads and writes [OK]

Hint: Keep only needed indexes to balance reads and writes [OK]

Common Mistakes:

• Removing all indexes hurting reads
• Adding too many indexes slowing writes
• Rebuilding indexes too often causing delays