How does an index optimize query execution?

An index is a data structure (usually a B-tree) that stores a small part of a table's data and references to the corresponding rows in the main table.

Optimization process:

1. Fast search: Instead of scanning the entire table (full table scan), the database management system (DBMS) examines the index. The index is sorted and optimized for quick searches on specific columns.
2. Reducing data read volume: The index contains only the indexed columns and pointers, significantly reducing the amount of data that needs to be read from disk compared to reading the entire row or data page.
3. No sorting needed: The index is already sorted by the indexed columns. This eliminates the need for costly sorting operations if the query requires sorted results.
4. Join operation optimization: When joining tables on indexed columns, the DBMS can use the index to quickly find matching rows in another table instead of performing nested loops over the entire table.

Example:

Without an index on the LastName column for the query SELECT * FROM Users WHERE LastName = 'Ivanov', the DBMS would have to read each row in the Users table and check the LastName value.

With an index on LastName, the DBMS quickly finds records where LastName equals 'Ivanov' by examining the sorted index, then uses pointers from the index to retrieve full row data from the main table.

-- Example query that benefits from an index on LastName
SELECT UserId, FirstName
FROM Users
WHERE LastName = 'Ivanov';

-- Example of creating an index to optimize the above query
CREATE INDEX IX_Users_LastName ON Users (LastName);