What are the types of indexes in SQL (e.g., unique, full-text)?

There are several types of indexes in SQL:

Unique Index: Ensures that all values in the indexed column(s) are unique. Automatically

created when a PRIMARY KEY or UNIQUE constraint is defined on a column.

Example:

CREATE UNIQUE INDEX idx_employee_id ON employees(id);

• Full-Text Index: Used for indexing large text fields. It allows for more advanced searches

like full-text searches (e.g., MATCH in MySQL).

Example (MySQL):

CREATE FULLTEXT INDEX idx_fulltext_desc ON products(description);

• ● Clustered Index: Defines the physical order of the data in the table based on the

index. Each table can have only one clustered index.

• Non-Clustered Index: An index that stores a separate structure containing the

indexed columns and pointers to the actual data rows. A table can have multiple

non-clustered indexes.

• Composite Index: An index that includes multiple columns. Useful for queries that

filter based on multiple columns.

• Spatial Index: Used for indexing spatial data types such as points, lines, and

polygons (mostly for geographical data).