Indexing

Database structure that improves the speed of data retrieval from a table. Think of it like an index in a book: instead of scanning every page, you can quickly jump to the location of the information you need.
Indexes do not store the actual data, but rather pointers to the data. They are especially useful for large tables where queries on certain columns are frequent.

Why Use Indexes

Faster SELECT queries on large tables.
Efficient searching and sorting.
Can improve performance for JOIN and WHERE clauses.

Indexing Trade-off

Indexes consume extra storage.
They can slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.
Improper use of index can make the query performance worst.

Single-Column Index

Index on a column

Given a table definition:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department_id INT
);

Create an index on emp_name.

CREATE INDEX idx_emp_name ON employees(emp_name);

Queries like the example below, will run faster because the database can use the index to quickly find matching rows.

SELECT * FROM employees WHERE emp_name = 'Alice';

Composite (Multi-Column) Index

Indexes can include multiple columns to optimize queries filtering on more than one column.

Index on multiple column

Given a table definition:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department_id INT
);

Create an index on department and name.

CREATE INDEX idx_dept_name ON employees(department_id, emp_name);

Queries like the example below, will run faster because the database can use the index to quickly find matching rows. The order of columns in the index matters for query optimization.
This is called the left most prefix rule, which sorts the index via department_id first then emp_name, see below:

department_id	emp_name
10	Allan
10	Alice
10	Baker
10	Clark
20	Diaz
20	Evans
30	Frank
30	Green

SELECT * FROM employees WHERE department_id = 10 AND emp_name = 'Alice';

Since the query looks for department_id = 10, It will be fast for the database engine to block records on department_id equals to 10 and then scan for emp_name equal to 'Alice'.

Unique Index

Enforces uniqueness while also speeding up searches.
Automatically created for PRIMARY KEY or UNIQUE constraints.

-- Ensures no duplicate emails exist and allows faster lookups.
CREATE UNIQUE INDEX idx_unique_email ON users(email);

tip

Indexes are like a lookup table for faster access.
Commonly used on columns involved in WHERE, JOIN, and ORDER BY.
Over-indexing can hurt performance on write-heavy tables.

Types of Indexes (B-Tree, Hash, Partial)

Databases support different index types, each optimized for different kinds of queries. Understanding these helps you choose the right index for performance.

B-Tree Index

The default index type in most relational databases (MySQL, PostgreSQL, Oracle, and SQL Server).
Stores values in a balanced tree structure.
Optimized for range queries, sorting, and general lookups.
Best for:
- =, <, <=, >, >=
- ORDER BY
- BETWEEN
- Prefix searches (LIKE 'A%')

-- Create a b-tree index on the user's last name
CREATE INDEX idx_users_lastname ON users(last_name);

-- This index speeds up queries like:
SELECT * FROM users WHERE last_name = 'Garcia';
SELECT * FROM users WHERE last_name > 'M';

Hash Index

Index type that uses a hash table internally.
Optimized for equality comparisons (=) only.
Best for:
- Equality or exact match.

-- Create a hash index on the user's email
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- This index speeds up queries like:
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

Partial Index (Filtered Index)

An index created on only a subset of rows.
Reduces index size and improves performance when only part of the table is queried frequently.
Best for:
- Columns with many NULLs.
- Frequently queried filtered data.
- Tables with mixed "active / inactive" rows.

-- Index only active records:
CREATE INDEX idx_active_orders ON orders (customer_id)
WHERE status = 'ACTIVE';

-- This index speeds up queries like the one defined belom, which does not index inactive rows, saving space:
SELECT * FROM orders WHERE status = 'ACTIVE' AND customer_id = 10;
--

Indexing Comparison

Index Type	Best For	Not Good For	Example Databases
B-Tree	Most general queries, ranges, sorting		MySQL, PostgreSQL, Oracle, and SQL Server
Hash	Equality lookups	Ranges, sorting	PostgreSQL, MySQL MEMORY tables
Partial	Filtering specific rows	Full-table indexing	PostgreSQL

Index Usage

Indexes can greatly improve query performance, but using them incorrectly can slow down your system or waste storage. This section teaches when you should create an index and when you should avoid one.

When to Use Indexes

Use indexes on columns frequently used in WHERE clauses

-- If a column is often used to filter results, an index improves performance
SELECT * FROM orders WHERE customer_id = 10;

-- Create an index on customer_id
CREATE INDEX idx_orders_customer ON orders(customer_id);

Use indexes on JOIN columns

-- Indexes speed up joins between large tables
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Index both sides of the join
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_customers_customer ON customers(customer_id);

Use indexes on UNIQUE columns to enforce constraints

-- A UNIQUE constraint automatically creates an index
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Use indexes for sorting (ORDER BY)

-- Indexes help when sorting large tables
SELECT * FROM products ORDER BY price;

-- Index for the sorted query
CREATE INDEX idx_products_price ON products(price);

Use indexes for range queries

-- B-Tree indexes are optimized for ranges
SELECT * FROM sales WHERE sale_date BETWEEN '2025-01-01' AND '2025-01-31';

-- Index for the query with range
CREATE INDEX idx_sales_date ON sales(sale_date);

When NOT to Use Indexes

Do NOT index small tables

Indexes add overhead with little benefit.
A table with 50 rows doesn’t need an index, the database can scan it faster.

Do NOT index columns with very low selectivity

Columns that have the same values in most rows don’t benefit from indexing.
Indexing columns like gender (Male / Female), status (ACTIVE / INACTIVE) and is_deleted (0 / 1) wastes space and slows writes. This is an example of columns that only have 2 distinct values.

Do NOT index columns that are frequently updated

Each update forces the index to update too.
If a column like current_temperature is updated many times per second (Like the SQL statement below), then avoid indexing current_temperature:

UPDATE sensors SET current_temperature = 82 WHERE sensor_id = 1;

Do NOT create indexes on large TEXT or BLOB columns

Avoid indexing columns like description and comment that uses TEXT and image that uses BLOB, they are slow and unnecessary.
A better approach is to use full-text search (FTS) via table or index definition.

-- Via Table definition

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
)

-- Via Index definition

CREATE FULLTEXT INDEX idx_fts ON articles(title, body);

Do NOT over-index a table

Each index slows down: INSERTS, UPDATES and DELETES.
This will hurt write performance significantly.

Summary

Good Reasons to Index	Bad Reasons to Index
Frequent WHERE filters	Low-selectivity columns
JOIN columns	Small tables
ORDER BY or GROUP BY	Frequently updated columns
Range queries	Large TEXT/BLOB
UNIQUE enforcement	Too many unnecessary indexes

EXPLAIN / ANALYZE Basics

Database queries don’t always run as efficiently as you expect. To understand how the database executes a query and whether it uses an index you use tools like EXPLAIN and EXPLAIN ANALYZE. This is essential when optimizing queries and deciding where to add indexes.
These commands show the query execution plan, which reveals:
- Which indexes are used.
- Whether a full table scan happens.
- Estimated vs actual cost.
- Join strategies.
- Row estimates.

EXPLAIN

Shows the estimated execution plan, what the database thinks it will do.
Command:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

Output TODO:

EXPLAIN ANALYZE

Runs the query and shows actual execution time and actual row counts.

EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Smith';

Output TODO:

EXPLAIN vs EXPLAIN ANALYZE

Feature	EXPLAIN	EXPLAIN ANALYZE
Runs the query?	No	Yes
Shows actual execution time?	No	Yes
Risk of modifying data?	Safe	Can modify data (for UPDATE/DELETE)
Good for?	Query planning	Performance tuning

Careful with EXPLAIN ANALYZE

Never use EXPLAIN ANALYZE with destructive queries (UPDATE / DELETE) unless you’re in a safe environment.

Value of Indexes

Without index
With index

Command:

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

Output TODO:

Adding an index:

CREATE INDEX idx_orders_customer ON orders(customer_id);

Command:

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

Output TODO:

Why Use Indexes​

Single-Column Index​

Composite (Multi-Column) Index​

Unique Index​

Types of Indexes (B-Tree, Hash, Partial)​

B-Tree Index​

Hash Index​

Partial Index (Filtered Index)​

Indexing Comparison​

Index Usage​

EXPLAIN / ANALYZE Basics​

EXPLAIN​

EXPLAIN ANALYZE​