Which two are true about indexes?

Question

Options

• ASecondary index access will always be faster than a table scan.
• BIndexing all of a table's columns improves performance.
• CIndexes contain rows sorted by key values.
• DIndexes reduce disk space used.
• EIndexes are used to enforce unique constraints.

• B
  92% (24)
• C
  4% (1)
• E
  4% (1)

Explanation

There appears to be an error in the provided answer key - C and E are the two correct statements, not B and D.

C is correct because indexes (specifically B-tree indexes, the most common type) store key values in sorted order, which enables fast binary-search-style lookups instead of scanning every row. E is correct because databases implement UNIQUE constraints using a unique index under the hood, rejecting inserts/updates that would create duplicate key values.

A is wrong because a secondary index can actually be slower than a table scan when a query returns a large percentage of rows - each index entry requires a separate random I/O to fetch the full row, which adds up. B is wrong because indexing all columns hurts performance: every INSERT, UPDATE, and DELETE must update every index, creating write overhead. D is wrong because indexes consume additional disk space on top of the table data - they are a space-for-speed tradeoff, not a space saver.

Memory tip: Think of an index like a book's index appendix - it takes up extra pages (D is false), helps you jump straight to the right page (C, sorted), enforces that each entry is unique if needed (E), but flipping back and forth for 90% of pages would be slower than just reading cover-to-cover (A is false), and indexing every single word would make the appendix bigger than the book itself (B is false).

No community discussion yet for this question.