What is the difference between a key and an index?

Difficulty: 3/10

primary key, secondary index, query optimization

Difference Between Keys and Indexes in MySQL

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.

Scenario Questions

0-2 years experience

Follow-up Questions