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 guarantees that INSERT operations finish faster because data is always written in sorted order.
It usually reduces the number of rows the optimizer must scan, speeding up queries that filter or sort on that column.
It automatically removes duplicate rows from the table, eliminating the need for separate deduplication steps.
It prevents the database from using sequential scans by disabling them in the execution plan.
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 index in a relational database?
Open an interactive chat with Bash
How does an index improve query performance in WHERE clauses?
Open an interactive chat with Bash
Does adding an index affect insert or update operations?