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).
A key is a constraint or rule applied to one or more columns.
Keys ensure data integrity and uniqueness.
Examples: PRIMARY KEY, UNIQUE KEY, FOREIGN KEY.
Every key automatically creates an index to enforce its rule.
An index is a physical data structure used to speed up data retrieval.
Indexes do not enforce rules; they only optimize query performance.
Indexes can be created manually without being a key.
Examples: BTREE index, FULLTEXT index, HASH index.
Purpose: Keys enforce constraints; indexes improve performance.
Logical vs Physical: Keys are logical constraints; indexes are physical structures.
Creation: Keys automatically create indexes; indexes can exist without keys.
Use Case: Keys guarantee correctness; indexes guarantee speed.
Here, idx_email speeds up searches on the email column but does not enforce uniqueness—because it is an index, not a key.