Index impact on INSERT and UPDATE in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

How do indexes affect INSERT operations?

When you insert a new row into a table that has multiple indexes, what happens behind the scenes?

A The database updates all indexes related to the table to include the new row, which can slow down the INSERT.

B The database duplicates the inserted row in each index without updating the main table.

C Only the primary key index is updated during INSERT; other indexes update later asynchronously.

D Indexes are ignored during INSERT operations to speed up data entry.

💡 Hint

Think about what must happen to keep indexes accurate when new data is added.

When inserting, the database must update all indexes to include the new row so queries using those indexes remain accurate. This adds overhead and can slow down INSERT operations.

🧠 Conceptual

intermediate

Why can UPDATE operations be slower on indexed columns?

Updating a column that is part of an index can be slower than updating a non-indexed column. Why?

A Because the database duplicates the entire table when an indexed column is updated.

B Because updates on indexed columns are blocked until all queries finish.

C Because the database must update the index entries related to the changed column, adding extra work.

D Because indexed columns cannot be updated once created.

💡 Hint

Consider what happens to the index when the data it tracks changes.

When an indexed column is updated, the database must update the index entries to reflect the new values, which adds overhead and can slow down the UPDATE operation.

❓ query_result

advanced

Effect of indexes on INSERT speed

Given a table users with 3 indexes, you run two INSERT queries: one on the table with indexes and one after dropping all indexes. Which query runs faster?

SQL

INSERT INTO users (id, name, email) VALUES (101, 'Alice', '[e-mail]');

A The INSERT on the table without indexes runs slower because indexes optimize data storage.

B The INSERT on the table without indexes runs faster because no index updates are needed.

C Both INSERTs run at the same speed because indexes do not affect INSERT performance.

D The INSERT on the table with indexes runs faster because indexes speed up data insertion.

💡 Hint

Think about the extra work indexes require during data insertion.

Indexes require updating during INSERT, which adds overhead. Without indexes, the database only inserts the row, so it is faster.

❓ query_result

advanced

Impact of updating indexed vs non-indexed columns

Consider a table products with an index on the price column. You run these two UPDATE queries:

UPDATE products SET price = price * 1.1 WHERE id = 5;
UPDATE products SET description = 'New model' WHERE id = 5;

Which UPDATE is expected to take longer?

A The UPDATE on the <code>price</code> column takes longer because it updates the index.

B The UPDATE on the <code>description</code> column takes longer because it is a text field.

C Both UPDATEs take the same time because indexes do not affect UPDATE speed.

D The UPDATE on the <code>description</code> column takes longer because it triggers index rebuilds.

💡 Hint

Consider which columns have indexes and what updating them requires.

Updating an indexed column requires updating the index entries, which adds overhead. Updating a non-indexed column does not affect indexes and is usually faster.

📝 Syntax

expert

Identify the error in index update during INSERT trigger

Given this trigger code to update a custom index table after INSERT, what error will it cause?

CREATE TRIGGER update_index AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_index(order_id, customer_id) VALUES (NEW.order_id, NEW.customer_id);
END;

A Runtime error because NEW keyword cannot be used in AFTER INSERT triggers.

B No error; the trigger works correctly.

C Logical error because the trigger causes infinite recursion.

D Syntax error due to missing delimiter declaration before trigger creation.

💡 Hint

Check if the trigger syntax requires special delimiter handling.

In many SQL dialects like MySQL, creating triggers requires changing the delimiter temporarily to avoid syntax errors. Missing this causes a syntax error.