What is the effect of a database index on performance?

What is the effect of a database index on performance?

• A. It speeds up lookups by providing a data structure to locate rows quickly.
• B. It adds overhead to writes and consumes extra storage.
• C. It eliminates the need for writes entirely.
• D. It slows down reads but speeds up writes.

Answer: (A)

Indexes speed up lookups by providing a data structure that lets the database locate rows quickly without scanning the entire table. They organize the indexed columns and store pointers to the full rows, so a query with a condition on those columns can jump straight to the relevant data rather than reading every row. This is why the best choice emphasizes faster lookups. Keep in mind the trade-off: maintaining the index adds overhead to writes and uses extra storage because the index must be updated whenever data changes. Writes aren’t eliminated, and while reads generally become faster, the act of writing can become a bit slower due to index maintenance.

Indexes speed up lookups by providing a data structure that lets the database locate rows quickly without scanning the entire table. They organize the indexed columns and store pointers to the full rows, so a query with a condition on those columns can jump straight to the relevant data rather than reading every row. This is why the best choice emphasizes faster lookups.

Keep in mind the trade-off: maintaining the index adds overhead to writes and uses extra storage because the index must be updated whenever data changes. Writes aren’t eliminated, and while reads generally become faster, the act of writing can become a bit slower due to index maintenance.