Can you explain the role of an index in a database and how it affects query performance?

A database index is a special data structure that speeds up the search and retrieval of records based on certain columns. It works like a pointer or a table of contents in a book: instead of scanning the entire table, the database quickly finds the needed rows using the index.

Advantages of indexes:

• Significantly accelerate the execution of SELECT queries with WHERE, JOIN, and ORDER BY clauses.
• Help maintain the uniqueness of values (e.g., unique keys).

However, indexes occupy additional space and slow down INSERT, UPDATE, DELETE operations, as the index needs to be updated.

Therefore, it is important to create indexes only on columns that are frequently used in search conditions and to regularly analyze their effectiveness.