Database Table Index (How Indexes Speed Up SQL Queries)

What Is a Database Index?

The File Cabinet Analogy

Consider a file cabinet where folders are stored in random order. Finding a specific folder requires checking every folder in the cabinet from front to back — a sequential scan that gets slower as the cabinet fills up. Now imagine sorting the folders alphabetically and adding labeled dividers at each letter. To find a folder starting with "K," you go directly to the K divider and search only that section. The dividers are the index — a sorted guide that eliminates the need to check every folder.
In a SQL database, the same principle applies. You decide how you will be querying the data — which columns appear in WHERE clauses, JOIN conditions, and ORDER BY clauses — and you create indexes on those columns to give the engine a sorted guide to follow instead of scanning every row.

How the Database Engine Uses an Index

Once an index is created, the database engine manages it automatically. You do not call the index in your SQL statements — you continue writing the same SELECT, WHERE, and JOIN syntax you always use. The engine's query planner examines your query, determines whether an available index can speed up the execution, and uses it if appropriate. The index is invisible to the SQL developer during normal query writing; its effect is felt only in execution speed.

As you work with SQL, you will find yourself paying increasing attention to query performance. You can optimize queries by selecting only the columns you need, reducing the number of rows returned, and simplifying joins — but for large tables, an index on the right column is often the single most effective performance improvement available.

How Indexes Work — The B-Tree Structure

The CREATE INDEX Statement

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Single-Column Index

A single-column index is the most common type. It indexes one column and speeds up queries that filter, sort, or join on that column:

CREATE INDEX idx_user_email
ON users (email);

CREATE INDEX idx_product_description
ON Products (Product_Description);

Composite Index — Column Order Matters

A composite index covers multiple columns. It is useful when queries frequently filter or sort on a combination of columns together:

CREATE INDEX idx_user_name_birthday
ON users (last_name, first_name, birthday);

CREATE INDEX idx_product
ON Products (Product_ID, Product_Description);

Types of Indexes

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

CREATE INDEX idx_upper_lastname
ON users (UPPER(last_name));

-- SQL Server only — not standard SQL
CREATE CLUSTERED INDEX idx_clustered_id
ON users (id);

CREATE NONCLUSTERED INDEX idx_nonclustered_name
ON users (last_name);

When to Create an Index

Index Maintenance and the Write Overhead

Keeping Statistics Up to Date

The query planner uses statistics about the distribution of data in each column to decide whether to use an index for a given query. These statistics include the number of distinct values in a column, the distribution of those values, and the total row count. If statistics are stale — reflecting the table's state from weeks ago rather than its current state — the planner may make poor decisions, choosing a full table scan when an index would be faster, or using an index when a full scan would be faster.

Most database systems update statistics automatically in the background (PostgreSQL autovacuum, MySQL InnoDB automatic statistics, SQL Server auto update statistics). After large bulk data loads or significant table modifications, it is good practice to manually trigger a statistics update — ANALYZE table_name in PostgreSQL, ANALYZE TABLE table_name in MySQL, or UPDATE STATISTICS table_name in SQL Server — to ensure the planner has accurate information.

Using EXPLAIN to Verify Index Usage

The EXPLAIN statement (or EXPLAIN ANALYZE in PostgreSQL) shows the query execution plan the engine chose for a given SQL statement — including whether it used an index or performed a full table scan. Running EXPLAIN before and after creating an index is the definitive way to confirm that the index is being used and is having the intended effect:

-- PostgreSQL / MySQL
EXPLAIN SELECT * FROM users WHERE email = 'gauss@example.com';

-- PostgreSQL with actual execution statistics
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'gauss@example.com';