Database Indexing easy
Why is it strongly recommended to index Foreign Key columns manually?
- A To enable 'Cascade Delete' and 'Cascade Update' operations that are strictly required by the ANSI SQL:2011 standard.
- B It is generally unnecessary, as modern database engines automatically create and manage indexes for every defined foreign key.
- C To accelerate JOIN operations and prevent expensive full table locks during deletions or updates on the parent table.
- D To enforce the structural integrity of the foreign key constraint itself and prevent the accidental insertion of orphaned records.
Answer: C. To accelerate JOIN operations and prevent expensive full table locks during deletions or updates on the parent table.
While a Foreign Key ensures data integrity, it does not automatically guarantee performance. Most JOIN operations look up records in the child table based on the parent's ID (`ON child.parent_id = parent.id`). Without an index on `child.parent_id`, every such join requires a slow O(N) scan. Furthermore, if you delete a parent record, the database must scan the entire child table to ensure no orphan records exist, which can cause significant locking issues in high-concurrency environments.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?