Which statement best describes how adding an index to a column that is frequently used in WHERE or ORDER BY clauses affects query performance in a relational database?
It prevents the database from using sequential scans by disabling them in the execution plan.
It usually reduces the number of rows the optimizer must scan, speeding up queries that filter or sort on that column.
It guarantees that INSERT operations finish faster because data is always written in sorted order.
It automatically removes duplicate rows from the table, eliminating the need for separate deduplication steps.
When an index exists on a column referenced in a WHERE or ORDER BY clause, the optimizer can use the index to locate rows in key order instead of scanning every row in the table. This generally reduces disk I/O and speeds up data retrieval, though it may add overhead to data-modification operations and consume extra storage. Options that describe removal of duplicates, faster INSERTs, or restrictions on execution plans are incorrect because indexes do not automatically deduplicate data, tend to slow (not speed) INSERTs due to maintenance overhead, and actually enable more efficient plans rather than limiting them.
Ask Bash
Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.
What is an execution plan (EP)?
Open an interactive chat with Bash
How does indexing improve query performance?
Open an interactive chat with Bash
What are the potential downsides of adding indexes?