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

Short answer: 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 code

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).

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.