What is the role of each index in a database and why are they needed?

Indexes in a database are used to speed up the search and retrieval of data. They are created on one or more columns of a table and allow the DBMS to quickly find the needed records without scanning all rows.

Main roles of indexes:

• Speeding up SELECT queries — an index allows quick lookup of rows by key value.
• Ensuring uniqueness — unique indexes guarantee that there are no duplicate values in a column.
• Optimizing sorting and grouping — indexes help perform ORDER BY and GROUP BY operations faster.

However, indexes occupy additional space and slow down insert, update, and delete operations because the index must be maintained in an up-to-date state.

Example of creating an index in SQL:

CREATE INDEX idx_user_email ON users(email);

This index will speed up searching for users by email.