Why Index impact on INSERT and UPDATE in SQL? - Purpose & Use Cases

The Big Idea

What if the very tool that speeds up your searches also slows down your data updates?

The Scenario

Imagine you have a big notebook where you write down all your friends' phone numbers. Every time you get a new number, you write it at the end. But when you want to find a number quickly, you have to flip through every page.

Now, if you want to add or change a number, you just write or erase at the end or somewhere in the notebook.

The Problem

Without any system to organize your notebook, finding numbers is slow. But if you try to keep an index or table of contents to find numbers faster, every time you add or change a number, you must also update the index. This makes adding or changing numbers slower and more complicated.

The Solution

Indexes in databases act like that table of contents. They help find data quickly. But when you add or change data, the database must also update these indexes. Understanding this helps you balance fast searches with smooth adding and updating.

Before vs After

✗ Before

INSERT INTO friends (name, phone) VALUES ('Alice', '12345'); -- simple insert, no index update

✓ After

CREATE INDEX idx_name ON friends(name);
INSERT INTO friends (name, phone) VALUES ('Alice', '12345'); -- insert updates index too

What It Enables

Knowing how indexes affect inserts and updates lets you design databases that are both fast to search and efficient to maintain.

Real Life Example

Think of an online store adding new products and updating prices often. If the product list is indexed for quick search, each addition or price change takes extra time to update the index, but customers find products faster.

Key Takeaways

Indexes speed up data searches but add work during inserts and updates.

More indexes mean slower data changes but faster queries.

Balancing indexes is key for good database performance.