What are the differences between logical keys (like candidate or composite keys) and physical indexes?

Question

Accepted Answer

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. Define the logical uniqueness rules of a table. Describe which columns can uniquely identify a row (candidate keys). Can include multiple columns (composite keys). Not all logical keys must be implemented physically. Exist at the schema design level, regardless of storage engine behavior. Candidate key → any minimal column set that uniquely identifies a row. Composite key → a key made of multiple columns (e.g., (order_id, product_id)). Primary key → the chosen candidate key for the table. Alternate keys → candidate keys not selected as the primary key. Actual data structures that MySQL stores on disk (B+Tree indexes for InnoDB). Used to speed up lookups, JOINs, sorting, filtering, and enforcing constraints. Created automatically for PRIMARY KEY and UNIQUE constraints. Can exist even when not tied to logical keys (e.g., an index just for performance). -- Logical keys defined during schema design:
-- Candidate keys: (email), (username)
-- Primary key: (user_id)

CREATE TABLE users (
    user_id INT PRIMARY KEY,         -- becomes a physical clustered index
    email VARCHAR(255) UNIQUE,       -- creates physical unique index
    username VARCHAR(255) UNIQUE,    -- creates physical unique index
    full_name VARCHAR(255)
);

-- Here, logical keys email and username map to physical indexes. Logical keys exist in the data model; physical indexes exist in storage. Logical keys describe uniqueness; indexes enforce uniqueness when tied to constraints. Composite logical keys may or may not become composite indexes. Indexes can exist without being logical keys (e.g., an index on order_date for performance). InnoDB physically organizes data by the PRIMARY KEY (clustered index), which is a physical index—not a logical concept. Logical keys guide schema design and define unique identification. Physical indexes determine query speed, access paths, and optimizer decisions. MySQL automatically turns certain logical constraints (PRIMARY KEY, UNIQUE) into indexes. 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.

What are the differences between logical keys (like candidate or composite keys) and physical indexes?

Differences Between Logical Keys and Physical Indexes in MySQL

What are the differences between logical keys (like candidate or composite keys) and physical indexes?

Differences Between Logical Keys and Physical Indexes in MySQL

Scenario Questions

Follow-up Questions