A query that runs thousands of times a day filters a 40-million-row customer table with WHERE email_address = ?. The execution plan shows a full table scan, and no index exists on email_address. What is the most effective fix?
Select an answer to reveal the explanation.
Short Explanation
Looking up one email in 40 million rows without an index is like finding a name in a phone book that was never sorted, page by page, every time. You give the engine an index on the column you filter on, and it jumps right to the entry. Sorting the output or waiting longer doesn't stop you from reading the whole book.
Full Explanation
An equality predicate on a column without a supporting index forces the engine to read every row and test each value, which is a full table scan. On a large table queried thousands of times a day, this consumes significant I/O, CPU and buffer memory. Creating an index on the filtered column lets the optimizer locate matching rows with an index seek that touches only a few pages, dramatically reducing cost. If email addresses must be unique, a unique index or constraint both supports the query and enforces the rule. The plan should be reviewed afterward to confirm the index is used. Adding ORDER BY on the same column asks the engine to sort the results; without an index it still scans the whole table and now also sorts, so cost increases rather than decreases. Switching to SELECT * returns more columns, which increases I/O and network transfer and can prevent covering indexes from being used; the number of columns requested does not reduce the rows that must be scanned. Raising the application timeout hides the symptom by letting slow queries finish, but every execution remains a full scan and resource use stays high. Exam caveat: indexes add overhead to writes, so heavily written tables need a balance, but a frequently executed selective lookup on a large table is a textbook case for an index. Operational check: create the index in a window, compare the execution plan and logical reads before and after, and monitor write latency on the table.