How an index works (B-tree mental model) in SQL

Introduction

An index helps the database find data faster, like a book's index helps you find a topic quickly.

When you want to speed up searching for a specific value in a large table.

When you often sort data by a certain column and want faster results.

When you join tables on a column and want quicker matching.

When you want to avoid scanning the whole table for queries.

When you want to improve performance of queries with WHERE conditions.

Syntax

SQL

CREATE INDEX index_name ON table_name (column_name);

An index is usually built on one or more columns of a table.

The database uses a B-tree structure to keep the index organized for fast searching.

Examples

This creates an index on the name column of the customers table to speed up searches by customer name.

SQL

CREATE INDEX idx_customer_name ON customers (name);

This index helps quickly find orders by their date.

SQL

CREATE INDEX idx_order_date ON orders (order_date);

This index speeds up queries filtering products by category.

SQL

CREATE INDEX idx_product_category ON products (category);

Sample Program

This example creates a table of employees, inserts some data, then creates an index on the department column. The EXPLAIN QUERY PLAN shows how the database uses the index to find employees in the Sales department faster.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50)
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales'),
(4, 'Diana', 'IT');

CREATE INDEX idx_department ON employees (department);

EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department = 'Sales';

Important Notes

A B-tree index works like a tree with branches and leaves, helping the database jump quickly to the right data instead of looking at every row.

Indexes speed up reads but can slow down writes because the index must be updated when data changes.

Not every query benefits from an index; sometimes a full table scan is faster for small tables.

Summary

An index is like a fast lookup guide for your data.

It uses a B-tree structure to quickly find values without scanning the whole table.

Creating indexes on columns you search often can make your queries much faster.