What are indexes in a database? Types of indexes (B-tree, Hash, Full-text). Clustered vs non-clustered indexes. Covering indexes.

Indexes in a database are special data structures that speed up searching and sorting records in tables, reducing the number of read operations.

Types of indexes:

• B-tree (balanced tree): the most common type. Suitable for range searches, sorting, equality. Efficient for most operations.
• Hash indexes: use hash functions for quick exact match searches. Do not support range searches.
• Full-text indexes: designed for full-text search in text fields, allowing searching for words and phrases within large texts.

Clustered vs non-clustered index:

• Clustered index determines the physical order of data in the table. Usually, a table can have only one clustered index.
• Non-clustered index is a separate structure that contains keys and pointers to data, not changing the physical order of rows.

Covering indexes:

An index that contains all the fields necessary to execute a query without accessing the main table. This significantly speeds up execution, as data is retrieved only from the index.

Example of creating a covering index in SQL:

CREATE INDEX idx_covering ON orders (customer_id, order_date) INCLUDE (total_amount);

Here, the index covers queries that use customer_id, order_date, and total_amount.