Can you name the types and kinds of indexes used in databases?

Databases have different types and kinds of indexes that help speed up data search and sorting. The main ones are:

• B-Tree index — the most common type, suitable for quick search, sorting, and range queries.
• Hash index — used for fast exact key lookups but does not support range queries.
• Bitmap index — effective for columns with a small number of unique values, often used in analytical systems.
• Full-text index — used for text search, supports searching by words and phrases.
• Spatial index — used for geographic data and spatial queries.

Indexes can also be:

• Unique — guarantee the uniqueness of values in a column.
• Composite (multi-column) — index multiple columns simultaneously.
• Clustered — define the physical order of data storage in a table.
• Non-clustered — a separate structure pointing to the data location.

Example of creating a B-Tree index in SQL:

CREATE INDEX idx_user_name ON users (name);

An index helps speed up queries like:

SELECT * FROM users WHERE name = 'Ivan';