In MySQL, logical keys describe the logical structure or rules of a table (such as candidate keys, composite keys, or superkeys), while physical indexes define how MySQL stores and organizes data for fast retrieval. Logical keys are part of data modeling, and physical indexes are part of database storage and optimization.

1. What Are Logical Keys?

1
Define the logical uniqueness rules of a table.
2
Describe which columns can uniquely identify a row (candidate keys).
3
Can include multiple columns (composite keys).
4
Not all logical keys must be implemented physically.
5
Exist at the schema design level, regardless of storage engine behavior.

2. Examples of Logical Keys

1
Candidate key → any minimal column set that uniquely identifies a row.
2
Composite key → a key made of multiple columns (e.g., (order_id, product_id)).
3
Primary key → the chosen candidate key for the table.
4
Alternate keys → candidate keys not selected as the primary key.

3. What Are Physical Indexes?

1
Actual data structures that MySQL stores on disk (B+Tree indexes for InnoDB).
2
Used to speed up lookups, JOINs, sorting, filtering, and enforcing constraints.
3
Created automatically for PRIMARY KEY and UNIQUE constraints.
4
Can exist even when not tied to logical keys (e.g., an index just for performance).

4. Example: Logical vs. Physical

5. Key Differences

1
Logical keys exist in the data model; physical indexes exist in storage.
2
Logical keys describe uniqueness; indexes enforce uniqueness when tied to constraints.
3
Composite logical keys may or may not become composite indexes.
4
Indexes can exist without being logical keys (e.g., an index on order_date for performance).
5
InnoDB physically organizes data by the PRIMARY KEY (clustered index), which is a physical index—not a logical concept.

6. How MySQL Uses Each

1
Logical keys guide schema design and define unique identification.
2
Physical indexes determine query speed, access paths, and optimizer decisions.
3
MySQL automatically turns certain logical constraints (PRIMARY KEY, UNIQUE) into indexes.
4
Foreign keys require physical indexes on child and parent columns.

In summary, logical keys define how data should behave, while physical indexes determine how MySQL stores and retrieves data efficiently. Both are related but serve different layers—logical design vs. physical performance.

Difficulty: 5/10

Topics: logical keys, physical indexes, MySQL schema design

Scenario Questions

0-2 years experience

1
You need to guarantee that (first_name, last_name) is unique in a MySQL table. How would you implement that, and what’s the difference between adding a UNIQUE constraint versus creating a separate index?
2
If you define a composite primary key on (order_id, product_id), how does MySQL store the rows internally and use that key for lookups?
3
We have an 'email' column that must be unique and also queried frequently. Explain why you might declare it as a UNIQUE key instead of just adding a regular index.

2-5 years experience

1
Our service is slow when filtering on a composite candidate key (user_id, region). The optimizer isn’t using the index you created. Walk me through why the logical key definition might not help and what you’d check or change.
2
During a migration we replaced a surrogate primary key with a composite key. After deployment, insert latency doubled. Why could the physical index layout cause this slowdown, and how would you address it?
3
A query that filters on just one column of a composite UNIQUE key ends up doing a full table scan. Explain why the logical key isn’t sufficient and what index adjustment you’d make.

5-8 years experience

1
We have a high‑traffic orders table with a composite primary key (customer_id, order_date) and several secondary indexes. At 10 M rows we see lock contention and index bloat. Discuss the trade‑offs of keeping that logical key as the primary key versus switching to a surrogate key with separate indexes.
2
Design a sharding strategy for a multi‑tenant SaaS where each tenant’s data is identified by a logical composite key (tenant_id, entity_id). How would you map those logical keys to physical indexes to keep joins fast while minimizing index size?
3
Our reporting service joins on a composite foreign key that mirrors a candidate key in another table. Explain the impact on join performance and how you’d structure indexes to avoid redundant physical indexes.

8+ years experience

1
Our legacy monolith uses many candidate keys as primary keys, resulting in large composite indexes. We’re moving to a microservices architecture with separate services per entity. Outline a migration roadmap to replace logical keys with surrogate primary keys and redesign physical indexes, covering data consistency, downtime, and future schema evolution.
2
In a multi‑region MySQL cluster we replicate tables that have composite primary keys. What challenges do physical index ordering and conflict resolution present, and how would you decide between keeping logical composite keys or switching to surrogate keys to ensure deterministic replication and low latency?

Follow-up Questions

❓What changes in query performance would you expect if you drop a UNIQUE constraint but keep its index?
❓How does InnoDB store a composite primary key compared to a single‑column surrogate key?
❓Can you describe a case where a candidate key should not be used as the primary key?