System-design

A Full-Text Index breaks text data into words (tokens) and indexes them for fast keyword and phrase searches, enabling efficient text search beyond simple exact matches or prefix queries.

A Hash Index uses a hash table internally to provide extremely fast equality lookups (e.g., WHERE key = value), offering average constant-time (O(1)) access by hashing keys to buckets, but it does not support range queries or sorting.

A Non-Clustered Index is a separate data structure that stores column values and pointers to table rows, speeding up lookups without altering the table's physical order. Multiple non-clustered indexes can exist per table to optimize diverse queries.

A Primary Index is an automatic, unique index created on a table's primary key for fast data retrieval. It ensures quick lookups, enforces uniqueness, and boosts performance in relational databases.

A Unique Index enforces distinct values in a column, preventing duplicates while speeding up queries. It's ideal for fields like email or username, ensuring data accuracy with minimal impact on read performance.