In this lesson, you will learn why adding too many indexes to a table acts as a "performance tax" on your write operations.
The Index Maintenance Overhead
Indexes are like a library catalog: they make finding a specific book (a SELECT query) incredibly fast. However, every time you add a new book to the library, the librarian must update every single catalog entry. In a database, every time you INSERT, UPDATE, or DELETE a row, the database engine must also navigate to the corresponding B-Tree structure for every secondary index and modify it to reflect the change.
If a table has only one index, the penalty is negligible. But if you have 10 or 15 indexes on a single table, a simple INSERT statement suddenly triggers 15 additional write operations behind the scenes.
Quantifying the Latency
Imagine a table storing customer orders. If you add indexes on customer_id, order_date, status, shipping_method, and total_amount, every new order requires the database to perform the primary insertion plus five secondary index updates.
-- This single command now triggers 6 separate write operations
-- (1 for the base table + 5 for the secondary indexes)
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (101, 55, '2023-10-27', 'PENDING', 99.99);
As your dataset grows, these B-Trees become deeper. The cost of maintaining them during writes increases exponentially, leading to higher CPU usage, increased I/O wait times, and—in high-concurrency environments—contention for locks. You might notice that your SELECT queries are lightning-fast, but your application feels sluggish during data entry or bulk updates.
Finding the Balance
The goal of index design is not to index every column that might be searched, but to index only what is necessary for your most critical query patterns. Before adding a new index, ask yourself if the read-performance gain outweighs the cumulative cost of every single write operation performed by your application. If a column is rarely part of a WHERE clause, the "hidden cost" of keeping its index updated will eventually dwarf the benefit it provides.
Key Takeaway: Every secondary index is an insurance policy that you pay for with every single write; minimize the overhead by indexing only for your most frequent and high-impact query patterns.