What is the role of indexes in an SQL database and why are they used?

Indexes in SQL databases are used to speed up data search and retrieval. They are created on one or more columns of a table and allow the database to find the needed records quickly without scanning the entire table (full table scan).

Indexes work like pointers or contents in a book: instead of reading the whole book, you go directly to the needed page. This significantly improves query performance, especially with large data volumes.

However, indexes occupy additional space and slow down insert, update, and delete operations because the index itself needs to be updated. Therefore, they are used selectively on columns that are frequently filtered, sorted, or joined.