What is an index in a database and what is it used for?

An index in a database is a special data structure that speeds up the search and retrieval of records based on certain table fields. Essentially, an index is a pointer that allows quick access to the needed data without scanning all rows.

Purpose of an index:

• Accelerates SELECT operations with WHERE conditions, JOINs, and ORDER BY sorting.
• Helps quickly find records by keys.

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

Example: if you have a users table with an email field, and you often search for users by email, creating an index on this field will significantly speed up the search.

In SQL, it might look like this:

CREATE INDEX idx_users_email ON users(email);

In summary: an index is a tool for optimizing query performance in a database.