A small e-commerce website uses a relational database to manage its products and orders. The most common query is retrieving a product by its unique product ID. This query is executed thousands of times per minute. The database currently has no indexes, and the query is slow, causing user-facing delays. The database administrator wants to improve performance with minimal downtime and cost. Which action should be taken first?
An index on the searched column accelerates lookups.
Why this answer
Adding an index on the product ID column creates a B-tree data structure that allows the database to locate rows using a logarithmic search instead of a full table scan. Since the query is executed thousands of times per minute and the table has no indexes, this single-column index directly addresses the bottleneck with minimal downtime and cost, as it requires only a CREATE INDEX statement.
Exam trap
The trap here is that candidates often choose increasing memory (Option C) because they confuse caching with indexing, not realizing that caching only helps after the first access and does not prevent full table scans on cache misses or for new data.
How to eliminate wrong answers
Option A is wrong because partitioning the table by product category would add complexity and overhead without directly speeding up lookups by product ID, and it requires significant schema changes and downtime. Option C is wrong because increasing server memory may improve caching of frequently accessed data, but it does not eliminate the need for a full table scan on a cold cache or first access, and it incurs hardware cost without addressing the root cause. Option D is wrong because migrating to a NoSQL system would require a complete architectural overhaul, significant downtime, and application rewrites, which contradicts the requirement for minimal downtime and cost.