Why does an index speed up search? What can it do?

A database index is a data structure that allows for quick lookup of rows in a table without having to scan all records sequentially. It works like a pointer or a table of contents in a book: instead of reading the entire book, you jump directly to the needed page.

Indexes are usually implemented using balanced trees (such as B-trees) or hash tables. When searching by an indexed column, the DBMS refers to the index, which contains sorted keys and references to the corresponding records, significantly reducing the number of read operations.

An index can:

• Accelerate search by key or range of values.
• Enforce uniqueness of values (unique indexes).
• Assist in sorting and grouping data.

However, indexes occupy additional space and slow down insert, update, and delete operations, as the index itself also needs to be updated.