What is the role of indexes when working with a database and what are they used for?

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 (full table scan).

Indexes work like pointers or a table of contents in a book: instead of reading the entire book, you immediately find the needed page. This significantly improves the performance of SELECT queries, especially with large volumes of data.

However, indexes occupy additional space on disk and slow down insert, update, and delete operations, as the index also needs to be updated. Therefore, it is important to create indexes only on columns that are frequently used in WHERE conditions, JOINs, or ORDER BY clauses.

Example of creating an index in SQL:

CREATE INDEX idx_user_email ON users(email);

This index will speed up the search for users by email.