Unique indexes in MySQL - Deep Dive

Overview - Unique indexes

What is it?

A unique index is a special rule in a database that makes sure no two rows have the same value in certain columns. It helps keep data clean by preventing duplicates. When you add a unique index to a column or group of columns, the database checks every new entry to keep values unique. This is useful for things like user emails or ID numbers where duplicates would cause problems.

Why it matters

Without unique indexes, databases could store duplicate data, causing confusion and errors. For example, if two users had the same email, it would be hard to tell them apart or send messages correctly. Unique indexes protect data quality and make searching faster. They help businesses trust their data and avoid costly mistakes.

Where it fits

Before learning unique indexes, you should understand basic database tables and how data is stored in rows and columns. After this, you can learn about primary keys, foreign keys, and how indexes improve query speed. Unique indexes are a step toward mastering data integrity and efficient database design.

Mental Model

Core Idea

A unique index is like a strict guest list that only allows one person with a specific name to enter a party, ensuring no duplicates.

Think of it like...

Imagine a guest list for a party where each guest's name must be unique. If someone tries to enter with a name already on the list, they are politely turned away. This keeps the party organized and avoids confusion about who is inside.

┌───────────────────────────────┐
│          Table Data            │
├─────────────┬───────────────┤
│ Column A    │ Column B      │
├─────────────┼───────────────┤
│ 1           │ alice@example │
│ 2           │ bob@example   │
│ 3           │ alice@example │  <-- Duplicate blocked by unique index
└─────────────┴───────────────┘

Unique Index on Column B prevents duplicate emails.

Build-Up - 7 Steps

1

Foundation

What is an index in databases

Concept: Introduces the idea of indexes as tools to speed up data lookup.

An index in a database is like an index in a book. It helps the database find data quickly without scanning every row. Think of it as a shortcut to find information fast.

Result

Queries that use indexed columns run faster because the database uses the index to jump directly to matching rows.

Understanding indexes as shortcuts helps grasp why databases use them to improve speed.

2

Foundation

Difference between normal and unique indexes

Concept: Explains that unique indexes add a rule to prevent duplicate values.

A normal index speeds up searches but allows duplicates. A unique index also speeds up searches but adds a rule: no two rows can have the same value in the indexed columns.

Result

Unique indexes enforce data uniqueness while still improving query speed.

Knowing that unique indexes combine speed and data integrity clarifies their dual purpose.

3

Intermediate

Creating unique indexes in MySQL

🤔Before reading on: do you think unique indexes can be created on multiple columns or only one? Commit to your answer.

Concept: Shows how to create unique indexes on one or more columns using SQL.

You can create a unique index on a single column or a combination of columns. For example: CREATE UNIQUE INDEX idx_email ON users(email); Or for multiple columns: CREATE UNIQUE INDEX idx_name_dob ON users(first_name, date_of_birth);

Result

The database will reject any insert or update that causes duplicate values in the indexed columns.

Understanding multi-column unique indexes helps enforce complex uniqueness rules beyond single fields.

4

Intermediate

Unique index vs primary key

🤔Before reading on: do you think a primary key is just a unique index or something more? Commit to your answer.

Concept: Compares unique indexes with primary keys and their roles.

A primary key is a special unique index that identifies each row uniquely and cannot be NULL. Unique indexes allow uniqueness but can accept NULLs (depending on the database). A table can have many unique indexes but only one primary key.

Result

Primary keys guarantee row identity, while unique indexes enforce uniqueness on other columns.

Knowing the difference helps design tables with proper keys and constraints.

5

Intermediate

How unique indexes affect inserts and updates

🤔Before reading on: do you think unique indexes slow down data insertion? Commit to your answer.

Concept: Explains the trade-off between data integrity and performance during data changes.

When you insert or update data, the database checks unique indexes to prevent duplicates. This check adds some overhead, so inserts and updates can be slower compared to tables without unique indexes.

Result

Unique indexes improve read speed but can slightly reduce write speed due to extra checks.

Understanding this trade-off helps balance performance and data correctness.

6

Advanced

Handling NULLs in unique indexes

🤔Before reading on: do you think unique indexes treat NULL values as duplicates or allow multiple NULLs? Commit to your answer.

Concept: Explores how NULL values behave in unique indexes in MySQL.

In MySQL, unique indexes allow multiple NULL values because NULL means unknown, so they are not considered equal. This means you can have many rows with NULL in a unique indexed column.

Result

Unique indexes enforce uniqueness only on non-NULL values, allowing multiple NULLs.

Knowing this prevents confusion when multiple NULLs appear in unique columns.

7

Expert

Unique index internals and locking behavior

🤔Before reading on: do you think unique index checks lock the entire table or just relevant rows? Commit to your answer.

Concept: Details how MySQL uses unique indexes internally and how locking works during transactions.

MySQL uses B-tree structures for unique indexes. When inserting or updating, it locks only the index entries it checks to prevent duplicates, not the whole table. This fine-grained locking improves concurrency but can cause deadlocks if not handled carefully.

Result

Unique index enforcement is efficient but requires understanding locking to avoid conflicts in high-traffic systems.

Understanding locking behavior helps design scalable, concurrent applications using unique indexes.

Under the Hood

Unique indexes are implemented using B-tree data structures that store keys in sorted order. When a new row is inserted or updated, the database searches the B-tree to find if the key already exists. If it does, the operation is rejected. The B-tree allows fast search, insert, and delete operations. Internally, the database locks only the relevant parts of the index during writes to maintain consistency without blocking the entire table.

Why designed this way?

Unique indexes were designed to combine fast data retrieval with data integrity enforcement. Using B-trees balances speed and storage efficiency. Locking only index entries rather than whole tables allows multiple users to work concurrently, improving performance. Alternatives like full table scans or hash indexes were less flexible or efficient for range queries and multi-column uniqueness.

┌───────────────┐
│   Table Rows  │
│  (unsorted)  │
└──────┬────────┘
       │
       ▼
┌─────────────────────┐
│    Unique Index      │
│  (B-tree structure)  │
│  Sorted keys stored  │
└──────┬──────────────┘
       │
       ▼
┌─────────────────────┐
│ Search for key fast  │
│ Check for duplicates │
└─────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a unique index prevent all duplicate data in a table? Commit to yes or no.

Common Belief: A unique index prevents any kind of duplicate data in the table.

Reality: A unique index only prevents duplicates in the columns it covers, not the entire table.

Why it matters: Assuming unique indexes cover all columns can lead to unexpected duplicates in other fields, causing data errors.

Quick: Can a unique index have multiple NULL values in MySQL? Commit to yes or no.

Common Belief: Unique indexes treat NULL as a value and allow only one NULL per column.

Reality: In MySQL, unique indexes allow multiple NULLs because NULL means unknown and is not considered equal to another NULL.

Why it matters: Misunderstanding this can cause confusion when multiple NULLs appear in supposedly unique columns.

Quick: Does adding a unique index always improve database performance? Commit to yes or no.

Common Belief: Adding a unique index always makes the database faster.

Reality: Unique indexes speed up reads but can slow down inserts and updates due to extra checks.

Why it matters: Ignoring this trade-off can cause performance problems in write-heavy applications.

Quick: Is a primary key just a unique index with a different name? Commit to yes or no.

Common Belief: Primary keys and unique indexes are exactly the same.

Reality: Primary keys are unique indexes that also enforce NOT NULL and identify rows uniquely; a table can have only one primary key but many unique indexes.

Why it matters: Confusing these can lead to poor database design and data integrity issues.

Expert Zone

1

Unique indexes on multiple columns enforce uniqueness on the combination, not individually, allowing duplicates in each column alone.

2

In InnoDB, the primary key is clustered with the data, so secondary unique indexes include the primary key to maintain row references.

3

Unique indexes can cause deadlocks in high-concurrency environments if transactions try to insert conflicting keys simultaneously.

When NOT to use

Avoid unique indexes when you expect many duplicates or NULLs that should be allowed freely. Instead, use normal indexes or no index. For enforcing complex rules, consider triggers or application logic. Also, avoid unique indexes on very large text columns due to performance and storage costs.

Production Patterns

In production, unique indexes are used to enforce business rules like unique usernames or emails. Composite unique indexes enforce uniqueness on combined fields like (country, phone_number). They are also used to optimize queries that filter on unique columns. Careful design avoids locking issues and balances read/write performance.

Connections

Primary keys

Primary keys are a special case of unique indexes with NOT NULL and single per table constraints.

Understanding unique indexes clarifies how primary keys enforce row identity and why they are essential for relational integrity.

Hash tables (Computer Science)

Both unique indexes and hash tables use key-based lookup to quickly find data.

Knowing how hash tables work helps understand the importance of fast key searches in unique indexes, even though databases use B-trees for range queries.

Legal contracts uniqueness clauses

Unique indexes enforce uniqueness like contract clauses prevent duplicate claims or rights.

Seeing unique indexes as legal rules helps appreciate their role in maintaining order and preventing conflicts in data.

Common Pitfalls

#1 Trying to insert duplicate values into a unique indexed column without handling errors.

Wrong approach:

INSERT INTO users (email) VALUES ('[e-mail]');
INSERT INTO users (email) VALUES ('[e-mail]');

Correct approach:

INSERT INTO users (email) VALUES ('[e-mail]') ON DUPLICATE KEY UPDATE email = email;

Root cause:

Not understanding that unique indexes reject duplicates and that insert errors must be handled.

#2 Creating a unique index on a nullable column expecting to prevent all duplicates including NULLs.

Wrong approach:

CREATE UNIQUE INDEX idx_phone ON contacts(phone_number); -- phone_number allows NULL

INSERT INTO contacts (phone_number) VALUES (NULL);
INSERT INTO contacts (phone_number) VALUES (NULL);

Correct approach:

Add NOT NULL constraint if duplicates including NULLs must be prevented:
ALTER TABLE contacts MODIFY phone_number VARCHAR(20) NOT NULL;
CREATE UNIQUE INDEX idx_phone ON contacts(phone_number);

Root cause:

Misunderstanding how NULLs are treated in unique indexes.

#3 Assuming unique indexes improve all query types equally.

Wrong approach:

Adding unique indexes on columns that are rarely searched or updated frequently without analysis.

Correct approach:

Analyze query patterns and add unique indexes only on columns used for lookups or enforcing uniqueness.

Root cause:

Lack of understanding of index impact on read/write performance balance.

Key Takeaways

Unique indexes ensure that values in one or more columns are unique across all rows, preventing duplicates.

They improve query speed on those columns but add overhead to inserts and updates due to uniqueness checks.

Unique indexes allow multiple NULLs in MySQL because NULL is treated as unknown and not equal to other NULLs.

Primary keys are special unique indexes that uniquely identify rows and cannot be NULL.

Understanding unique indexes helps design reliable, efficient databases that maintain data integrity.