In MySQL, the terms 'key' and 'index' are closely related but not identical. All keys create indexes, but not all indexes are keys. A key has a logical purpose (data integrity, uniqueness, relationships), while an index has a performance purpose (speeding up lookups).

1. What Is a Key?

1
A key is a constraint or rule applied to one or more columns.
2
Keys ensure data integrity and uniqueness.
3
Examples: PRIMARY KEY, UNIQUE KEY, FOREIGN KEY.
4
Every key automatically creates an index to enforce its rule.

2. What Is an Index?

1
An index is a physical data structure used to speed up data retrieval.
2
Indexes do not enforce rules; they only optimize query performance.
3
Indexes can be created manually without being a key.
4
Examples: BTREE index, FULLTEXT index, HASH index.

3. Key Differences

1
Purpose: Keys enforce constraints; indexes improve performance.
2
Logical vs Physical: Keys are logical constraints; indexes are physical structures.
3
Creation: Keys automatically create indexes; indexes can exist without keys.
4
Use Case: Keys guarantee correctness; indexes guarantee speed.

Example: Index Without a Key

Here, idx_email speeds up searches on the email column but does not enforce uniqueness—because it is an index, not a key.

Difficulty: 3/10

Topics: primary key, secondary index, query optimization

Scenario Questions

0-2 years experience

1
You have a users table with an id column as AUTO_INCREMENT. You also need each email to be unique and queries by email to be fast. How would you use keys and indexes to satisfy both requirements?
2
If you create a PRIMARY KEY on column id and then add a separate INDEX on id, what does MySQL actually store? Is there any redundancy?
3
What happens when you query a column that has a UNIQUE KEY but no explicit INDEX—does MySQL still use an index?

2-5 years experience

1
We added an INDEX on a status column, but queries filtering by status are still slow. How would you verify whether the index is being used, and why might the distinction between a key and an index matter here?
2
During a rollout a query that used to be fast started timing out after we added a composite index. Explain how MySQL treats primary keys versus secondary indexes and why that change could affect the query plan.
3
A developer removed a PRIMARY KEY from a table and replaced it with a UNIQUE KEY on the same column. What differences should you expect in storage and query performance?

5-8 years experience

1
Our service stores billions of rows. Should we use a natural primary key (clustered) or a surrogate key with a separate secondary index for lookups? Discuss trade‑offs in storage, write amplification, and range scans.
2
We need to partition a large table but also enforce uniqueness across partitions. How does the difference between a clustered primary key and non‑clustered secondary indexes influence our partitioning strategy?
3
A high‑write workload is suffering from index contention. Explain how the fact that secondary indexes store the primary key as a pointer can impact write latency.

8+ years experience

1
The company is migrating from MySQL InnoDB to a distributed SQL system. How would you map the concepts of primary keys and secondary indexes to ensure data consistency and query performance across shards?
2
Multiple microservices have created overlapping indexes on the same columns, leading to maintenance overhead. Propose a governance model that leverages the distinction between keys and indexes to standardize schema design across teams.
3
We are planning a long‑term schema evolution that will replace several primary keys with UUIDs. What architectural considerations around index size, replication, and query patterns should guide this migration?

Follow-up Questions

❓How does InnoDB physically store a primary key versus a secondary index?
❓What effect does adding a secondary index have on write performance?
❓When would you choose a UNIQUE index instead of a PRIMARY KEY?