SQL Indexes: Complete Performance Optimization Guide

1. What is an Index in SQL?

An index is a specialized data structure that the database engine maintains to locate rows rapidly without reading every row in a table.

Consider a 1,000-page book. If you want to locate every mention of the term "transactions" without an index, you must inspect every page from page 1 to 1,000. In databases, this exhaustive process is called a Full Table Scan. With an alphabetical index at the back of the book, you look up "transactions" in seconds, identify the exact page numbers, and navigate directly to those pages. In database terms, this targeted operation is an Index Seek.

Indexes convert linear table scans with O(N) time complexity into tree lookups with O(log N) time complexity. However, this speed comes with trade-offs:

Disk Space: Indexes consume secondary storage space alongside the physical table data.
Write Overhead: Every INSERT, UPDATE, and DELETE command must update both the base table rows and all associated index trees.

`The Index Balance Rule`

Read Speed:Indexes accelerate SELECT queries with WHERE filters, JOIN keys, and ORDER BY clauses.
Write Cost:Too many indexes slow down data ingestion and bulk batch inserts.

2. CREATE INDEX

To create an index on a specific column, use the standard CREATE INDEX statement:

CREATE INDEX index_name ON table_name (column_name);

You can verify how the database engine plans to execute a query using the EXPLAIN QUERY PLAN command.

Creating an Index and Inspecting Query Plan

SQL

Build a single-column index on salary and verify execution plan utilization

`Scan vs Seek`

SCAN TABLE:The engine inspects every page in the table sequentially.
SEARCH TABLE USING INDEX:The engine traverses the index tree directly to matching rows.

3. UNIQUE Index

A UNIQUE index performs two distinct duties: it accelerates query lookups and enforces data integrity by rejecting duplicate values in the indexed column.

CREATE UNIQUE INDEX index_name ON table_name (column_name);

When you define a PRIMARY KEY or UNIQUE constraint during table creation, relational database engines automatically generate a unique index behind the scenes.

Enforcing Uniqueness with UNIQUE Index

SQL

Ensure email uniqueness while enabling instant point lookups

4. Composite (Multi-column) Index

A composite index includes two or more columns from the same table. It is designed for queries that filter or sort by multiple columns simultaneously.

CREATE INDEX idx_emp_dept_salary ON employees (department_id, salary);

The Leftmost Prefix Rule

The order of columns defined in a composite index is significant. The database engine can use the composite index only if the query conditions match columns starting from the leftmost column in the index definition:

Filters on (department_id): Uses the index.
Filters on (department_id, salary): Uses the index.
Filters on only (salary): Cannot use the index for direct seeks because the entries are sorted primarily by department_id first.

Evaluating Multi-Column Composite Indexes

SQL

Accelerate compound WHERE filters using a two-column index structure

`Column Ordering Strategy`

Equality Filters First:Place columns filtered with equal signs (=) at the beginning of the index.
Range Filters Second:Place columns filtered with range comparisons (>, <, BETWEEN) after equality columns.

5. Clustered vs Non-Clustered Index

Relational databases organize stored data pages using two primary index architectures:

Property	Clustered Index	Non-Clustered Index
Physical Row Storage	Stores data rows directly in index leaf pages	Stores keys plus row pointers to the base table
Allowed Count per Table	Exactly 1 per table	Multiple allowed per table
Default Generation	Assigned to PRIMARY KEY in most engines	Created explicitly or via UNIQUE constraints
Lookup Overhead	Direct access to all columns on leaf node	Requires secondary pointer lookup to fetch non-indexed columns

`Storage Architecture Differences`

Single Clustered Order:Because physical table pages on disk can only be arranged in one physical order, each table can possess only one clustered index.
Secondary Lookup Cost:When a non-clustered index matches a row, it reads the row pointer to fetch missing columns from the clustered index.

6. How Indexes Work (B-Tree Internals)

The default structure for relational database indexes is the self-balancing B-Tree (Balanced Tree).

A B-Tree organizes keys across three tiers of nodes:

Root Node: The entry point of the index tree. Directs searches to appropriate branch nodes based on value ranges.
Branch (Intermediate) Nodes: Routing nodes that subdivide key spaces into smaller sub-intervals.
Leaf Nodes: The bottom tier containing all sorted keys. In non-clustered indexes, leaf nodes contain keys paired with row pointers. In clustered indexes, leaf nodes store the actual data rows.

Because the tree is balanced, every path from root to leaf has the exact same depth. A B-Tree with a depth of 3 or 4 can index millions of rows, meaning the database engine finds any target row in just 3 or 4 page reads.

In addition, leaf nodes are linked sequentially in a doubly linked list. This enables fast range queries (such as WHERE price BETWEEN 20 AND 50) by traversing directly from one leaf page to the next without returning to the root.

7. Covering Index

A covering index contains all columns required to satisfy a specific query, including columns used in SELECT, WHERE, JOIN, and ORDER BY clauses.

When an index is covering, the database engine retrieves all necessary data directly from the index leaf pages. It completely skips accessing the physical table pages, eliminating secondary row lookups and minimizing disk I/O.

Zero Table Lookup with a Covering Index

SQL

Satisfy all SELECT and WHERE columns entirely inside the index tree

`The INCLUDE Clause`

Non-Key Columns:Databases like PostgreSQL and SQL Server support CREATE INDEX idx ON table(col1) INCLUDE (col2).
Payload Storage:Included columns are stored in leaf pages without expanding intermediate branch nodes, saving index memory.

8. Full-Text Index

Standard B-Tree indexes only optimize exact comparisons (=) or prefix wildcard patterns (LIKE 'smith%'). When a query searches for terms inside long text fields using leading wildcards (LIKE '%wireless%'), a B-Tree cannot help and forces a Full Table Scan.

A Full-Text Index solves this challenge by parsing text documents into individual word tokens and building an Inverted Index. The inverted index maps each distinct word token directly to the list of row identifiers containing that word.

PostgreSQL: Uses tsvector data types with GIN (Generalized Inverted Index) structures.
MySQL: Supports FULLTEXT indexes queried with MATCH(...) AGAINST(...).
SQLite: Implements full-text search extensions via FTS5 virtual tables.

`B-Tree vs Full-Text Indexing`

B-Tree:Optimal for numbers, dates, exact keys, and prefix strings.
Inverted Index (Full-Text):Optimal for articles, product descriptions, blogs, and multi-word phrase matching.

9. DROP INDEX

When an index is no longer utilized or its maintenance cost exceeds query performance benefits, delete it using the DROP INDEX statement:

-- PostgreSQL and SQLite syntax
DROP INDEX IF EXISTS index_name;

-- MySQL and SQL Server syntax
DROP INDEX index_name ON table_name;

Removing an Index

SQL

Drop an obsolete index cleanly without modifying underlying table data

10. When Not to Use an Index

Adding an index is not always the correct decision. In several operational scenarios, indexes harm system performance:

Scenario	Why Indexing Fails	Recommended Alternative
Small Tables (< 1,000 rows)	Table scan in memory is faster than tree traversal	Rely on sequential in-memory memory scans
Low Cardinality (e.g., Boolean flags)	Filtered set contains 50% of table; optimizer ignores index	Use filtered or partial indexes on rare values
Write-Heavy Tables (Logs, Telemetry)	Every insert incurs page splits and tree write locks	Minimize indexes; index only during batch queries
Infrequently Filtered Columns	Consumes disk storage and buffer pool memory with zero ROI	Index only columns actively present in SLA queries
Queries Returning > 25% of Rows	Random disk I/O from index lookups is slower than sequential read	Allow optimizer to execute a sequential full scan

`Index Strategy Checklist`

Foreign Keys:Always index foreign key columns to optimize JOIN performance and prevent cascade locks.
Query Plan Profiling:Profile queries with EXPLAIN before and after adding indexes to verify actual performance improvements.

Knowledge Check: SQL Indexes

1. What data structure do relational databases standardly use for indexes?

2. Why do indexes slow down INSERT statements?

3. How many clustered indexes can a table have?

4. What is the leftmost prefix rule in composite indexes?

5. What makes an index a covering index?

6. When should you avoid creating an index?