Problem Statement
Why should foreign key columns be indexed in write-heavy OLTP tables, and what problems appear if they are not?
Explanation
When a parent row changes or is deleted, the engine must verify or locate matching child rows. Without an index, it scans the whole child table, which is slow and holds locks longer. That increases contention and deadlock risk.
An index on the foreign key lets the engine seek directly to the children. It speeds joins, shortens lock durations, and keeps hot paths predictable under load.
Code Solution
SolutionRead Only
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Practice Sets
This question appears in the following practice sets:
