What role does an index play in a database and why is it used?

An index in a database is a data structure that speeds up the search and retrieval of records based on certain fields (columns). Without an index, searching requires a full scan of all rows (full table scan), which is slow with large volumes of data.

Indexes allow:

• Quickly finding records by key.
• Accelerating sorting and table join operations.

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

Example: if you have a user table with an email field, creating an index on email will allow you to quickly find a user by email address.

CREATE INDEX idx_email ON users(email);

Thus, an index is a tool for query optimization in a database.