Skip to content
Quantoros

What is an index scan vs a sequential scan?

SQLLevel: IntermediateDatabase Design

What is an index scan vs a sequential scan?

Hints
  • How is lookup performed without the structure and what changes with it?
  • Check selectivity, key order and the cost of data updates.

Answer

A sequential scan reads every row of the table from start to finish. It's efficient when you need a large fraction of the table or when the table is small.

An index scan uses an index to find matching rows and then fetches only those rows. It's efficient for selective queries (where few rows match).

Counterintuitive fact: for a query that returns 40% of the table, a sequential scan is often faster than an index scan, because the index scan requires many random disk reads while the sequential scan does a single large sequential read.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions