What types of index in SQL?

In SQL, there are several types of indexes, each serving different purposes for optimizing query performance. Below are the most common types of indexes:

1. Primary Index (Clustered Index)

• Definition: A clustered index determines the physical order of data in the table. The rows are stored on the disk in the same order as the index. Every table can have only one clustered index because the data rows can only be sorted in one way.
• Use Case: Typically created on the primary key column (but can be created on any column). It speeds up access to rows by the indexed column.
• Example: A table with a PRIMARY KEY automatically has a clustered index.

CREATE TABLE employees (
    id INT PRIMARY KEY,   -- Automatically creates a clustered index on id
    name VARCHAR(100),
    age INT
);

2. Secondary Index (Non-Clustered Index)

• Definition: A non-clustered index is separate from the actual data rows and stores a reference to the data. It doesn't affect the physical order of the data in the table. A table can have multiple non-clustered indexes.
• Use Case: Used to speed up queries that access columns frequently in the WHERE, ORDER BY, or JOIN clauses, especially when these columns aren't part of the primary key.
• Example:

CREATE INDEX idx_employee_name
ON employees (name);  -- Non-clustered index on the 'name' column

3. Unique Index

• Definition: A unique index ensures that the values in the indexed column(s) are unique across the table, meaning no duplicate values can exist in the indexed columns.
• Use Case: Typically used for enforcing the uniqueness of values (like email or username) in a table. Unique indexes are automatically created for primary and unique keys.
• Example:

CREATE UNIQUE INDEX idx_unique_email
ON employees (email);  -- Ensures that 'email' is unique across the table

4. Composite Index (Multi-Column Index)

• Definition: A composite index (or multi-column index) is an index on two or more columns of a table. It helps speed up queries that filter or sort on multiple columns.
• Use Case: When queries frequently filter or sort by multiple columns, a composite index can improve performance.
• Example:

CREATE INDEX idx_employee_name_age
ON employees (name, age);  -- Composite index on 'name' and 'age'

5. Full-Text Index

• Definition: A full-text index is used for text searching in large textual data (e.g., to search for specific words or phrases within text-heavy columns). This index type is optimized for performing full-text searches like finding matches for word prefixes or exact words.
• Use Case: Typically used for columns containing large text data such as articles, blog posts, or comments.
• Example:

CREATE FULLTEXT INDEX idx_fulltext_description
ON articles (description);  -- Full-text index on the 'description' column

6. Bitmap Index

• Definition: A bitmap index uses a bitmap for each distinct value in a column. It's particularly efficient for columns with a low cardinality (few unique values), such as gender, status, or boolean fields.
• Use Case: Effective for complex queries involving multiple conditions on low-cardinality columns.
• Example:

CREATE BITMAP INDEX idx_status
ON employees (status);  -- Bitmap index on 'status' (e.g., 'active', 'inactive')

7. Spatial Index

• Definition: A spatial index is used for spatial data types (e.g., points, polygons, etc.) to speed up spatial queries like distance or area calculations.
• Use Case: Used in geographic databases where spatial relationships (e.g., within a certain distance, inside a region) need to be queried.
• Example:

CREATE SPATIAL INDEX idx_location
ON locations (geo_data);  -- Spatial index on a geographical data column

8. Reverse Key Index

• Definition: A reverse key index is an index that stores the reversed value of the indexed column, which can help prevent hotspotting (when multiple queries access the same part of the index).
• Use Case: Often used in cases where there is a high volume of inserts and the indexed column has a sequential or predictable value (e.g., incrementing numbers).
• Example:

CREATE INDEX idx_reverse_key_id
ON employees (id) REVERSE;  -- Reverse key index on 'id'

9. Clustered Index vs Non-clustered Index

• Clustered Index: Determines the physical order of data rows in the table. Only one clustered index can exist on a table.
• Non-clustered Index: Doesn't affect the physical order of data; a table can have multiple non-clustered indexes.

Summary of Index Types:

• Clustered Index: One per table, organizes data physically.
• Non-clustered Index: Multiple per table, separate structure from data.
• Unique Index: Ensures uniqueness of column values.
• Composite Index: Index on multiple columns.
• Full-Text Index: Used for text search in large text columns.
• Bitmap Index: Efficient for low-cardinality columns.
• Spatial Index: Optimized for spatial data types.
• Reverse Key Index: Reverses values for better distribution.

Indexes are critical for performance, but they should be used judiciously as they introduce storage overhead and can slow down data modifications (insert, update, delete).