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

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 database to quickly find the needed records without scanning all rows.

Role of indexes:

• Improve the performance of SELECT queries, especially with WHERE, JOIN, ORDER BY conditions.
• Help the query optimizer choose efficient execution plans.

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

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