What best describes the relationship between indexes and SQL performance?
-
Indexes are only used in special cases
-
Indexes are used to make table storage more efficient
-
Indexes rarely make a difference in SQL performance
-
Indexes exist solely to improve query speed.
Indexes are database objects created specifically to accelerate data retrieval operations. They work like book indexes, allowing the database engine to locate rows quickly without scanning entire tables. While indexes do require additional storage and can slow down INSERT/UPDATE/DELETE operations, their primary and sole purpose is query performance optimization.
Among the choices, 'indexes exist to improve query speed' is the closest fit to why indexes are added to a database — they let the engine locate rows via a sorted/structured lookup (e.g. B-tree) instead of scanning the whole table, dramatically speeding up SELECTs, WHERE clauses, JOINs, and ORDER BY. The other options are wrong: indexes are not primarily about storage efficiency (they add storage overhead), they are not 'rarely' impactful (they're one of the single biggest performance levers in SQL), and they are not 'only used in special cases' — they're a standard, widely-used tool. Note the word 'solely' is a slight overstatement (indexes can also help enforce uniqueness), but it's still the best of the four options.