Indexes Deep Dive

An index is a data structure used to speed up the retrieval of records from a database table. Think of it like an index at the back of a book.

Types of Indexes

1. Clustered Index: Defines the physical order in which data is stored in the table. A table can have only one.
2. Non-Clustered Index: Stores a separate list of values with a pointer to the actual data row. Multiple can exist per table.
3. Unique Index: Ensures that no two rows have the same value in the indexed column.
4. Composite Index: An index on multiple columns.

Clustered Index

The clustered index is the table. Data pages are sorted and stored according to the index key. When you create a PRIMARY KEY, most databases automatically create a clustered index on it.

-- SQL Server: Create clustered index explicitly
CREATE CLUSTERED INDEX IX_Orders_Date ON Orders(order_date);

Only one clustered index per table because data can only be physically sorted one way.

Non-Clustered Index

A non-clustered index is a separate structure containing the indexed columns plus a pointer (row locator) to the actual data row. It is like the index at the back of a textbook — you look up the topic and get a page number.

CREATE NONCLUSTERED INDEX IX_Employees_Name ON Employees(name);

Composite (Multi-Column) Index

An index on multiple columns. The leftmost prefix rule applies: the index can be used for queries that filter on the first column, first + second, or all columns.

CREATE INDEX IX_Orders_UserDate ON Orders(user_id, order_date);

This index can optimise:

• WHERE user_id = ? (uses first column)
• WHERE user_id = ? AND order_date = ? (uses both)
• But NOT WHERE order_date = ? alone (skips the first column)

Covering Index

A covering index includes all columns needed by a query (both SELECT and WHERE). The database can satisfy the query entirely from the index without touching the main table. This is the fastest type of index access.

-- If the query is:
SELECT name, salary FROM employees WHERE department_id = 5;

-- A covering index would be:
CREATE INDEX IX_Covering ON employees(department_id) INCLUDE (name, salary);

Index Data Structures

Type	Best For
B-Tree	High-cardinality data (IDs, names, emails) — range queries, sorting
Hash	Exact match lookups (WHERE id = ?)
Bitmap	Low-cardinality data (gender, status) — data warehouses
GiST/GIN	Full-text search, arrays, JSON (PostgreSQL)

When to Add an Index

• Columns used frequently in WHERE, JOIN, or ORDER BY.
• Columns with high selectivity (many unique values).
• Foreign key columns.

When to Avoid Indexes

• Small tables (full scan is cheaper).
• Columns rarely used in queries.
• Columns with very low selectivity (e.g., boolean flags — unless combined with other columns).