In this lesson, you will learn why enforcing data uniqueness adds a distinct performance tax to your write operations compared to non-unique indexes.
The Cost of Uniqueness
When you create a standard (non-unique) index, the database engine simply inserts the new value into the B-Tree structure. If another row already has that value, the database happily adds a new entry to the leaf node, potentially even creating a new leaf page if the current one is full.
However, a Unique Index changes the workflow significantly. Before the database can finalize an insert or update, it must perform a mandatory "existence check."
The "Read-Before-Write" Penalty
Every time you perform a write operation on a column with a unique constraint, the engine must traverse the B-Tree to confirm that the specific value does not already exist.
Consider this operation:
INSERT INTO Users (Email) VALUES ('newuser@example.com');
For a non-unique index, the database finds the correct leaf page and performs the write. For a unique index, the database must:
- Search: Traverse the index tree from the root to the leaf.
- Verify: Check if the value
'newuser@example.com'is already present. - Write: If the check confirms the value is absent, it proceeds with the insert.
Why This Matters
This "read-before-write" step introduces two main overheads:
- Latency: You are essentially performing a search operation on every write. Even if the data is cached in memory, this adds CPU cycles and increases the duration of the transaction.
- Contention: If multiple transactions attempt to insert or modify values that map to the same part of the
B-Tree, the uniqueness check creates a bottleneck. Because the database must guarantee absolute uniqueness, it often places locks on the index range to prevent "phantom" inserts during the validation process.
Non-unique indexes avoid this verification step entirely, allowing for faster, "blind" inserts where the database doesn't care about the existing contents of the index leaf pages. While unique indexes are vital for data integrity, they require more physical IO and locking overhead than their non-unique counterparts.
Key Takeaway: Unique indexes require an mandatory search operation before every write, trading off write performance for the guarantee of data integrity.