KAHIBARO
Discord Login Register ☰
☰

10.14 Indexes

Table of Contents

Why Indexes Matter in SQL

Indexes are one of the most important tools for database performance. They can make queries hundreds or thousands of times faster, but they can also slow down writes if used incorrectly.

In this chapter you will learn what indexes are, how they work conceptually, and how to use them effectively in SQL, with a focus on relational databases like PostgreSQL and MySQL.

Key idea: An index speeds up reading data, but adds overhead to writing data.
Use indexes to speed up queries, not just because you can.

What Is an Index?

Think of an index in a book. If you want to find the page that mentions "binary trees", you do not read every page. You go to the index at the back of the book, find "binary trees", and it tells you the pages.

A database index plays the same role for tables:

Without an index, the database often needs to scan the whole table to find matching rows. This is called a sequential scan or full table scan.

With an index, the database can often jump directly to the needed rows, using a faster search algorithm, usually something like a balanced tree.

How Indexes Work Conceptually

You do not need to know the exact internal algorithms to use indexes well, but you should understand the basic idea.

Most relational databases use B-tree indexes by default.

Table vs Index

Imagine a simple users table:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    username    VARCHAR(50)  NOT NULL,
    created_at  TIMESTAMP    NOT NULL
);

The data lives in a table like this:

idemailusernamecreated_at
1a@example.comalice2024-01-01 10:00:00
2b@example.orgbob2024-01-02 11:00:00
3charlie@example.iocharlie2024-01-03 12:00:00
…………

If you run:

sql 
SELECT * FROM users WHERE email = 'b@example.org';

The index is a separate structure, stored by the database engine.

You can imagine an index on email like this:

emailrow pointer
a@example.com1
b@example.org2
charlie@example.io3
……

The row pointer tells the database where to find the actual row in the table.

Creating and Dropping Indexes

You usually create indexes explicitly, except for primary keys and some unique constraints that create them automatically.

Creating a Simple Index

Syntax:

sql 
CREATE INDEX index_name ON table_name (column_name);

Example:

sql 
CREATE INDEX idx_users_email ON users (email);

Now queries that filter by email can use this index:

sql 
SELECT * FROM users WHERE email = 'bob@example.org';

The database can choose to use the index if it thinks it is faster.

Dropping an Index

If an index is unused or harmful to performance, you can remove it:

sql 
DROP INDEX idx_users_email;

In some databases, like MySQL, the index name is scoped to the table, and you often use:

sql 
ALTER TABLE users DROP INDEX idx_users_email;

Always check your specific database syntax, but the concept is the same.

Primary Keys, Unique Constraints, and Indexes

Some constraints automatically create indexes.

Primary Key

When you define a primary key:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    ...
);

The database automatically creates an index on id. This index:

You rarely need to create an extra index on a primary key column, because it already has one.

Unique Constraint

If you define a unique constraint:

sql 
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

The database creates a unique index under the hood.

A unique index:

Rule: A PRIMARY KEY or UNIQUE constraint already has an index. Do not create a second index on the same column set.

When Indexes Help

Indexes are useful when queries frequently:

Simple Filter Example

Table:

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  TIMESTAMP   NOT NULL
);

Query:

sql 
SELECT *
FROM orders
WHERE user_id = 42;

If you frequently query by user_id, create an index:

sql 
CREATE INDEX idx_orders_user_id ON orders (user_id);

Now the database can quickly find all orders for one user.

Multiple Filters Example

Query:

sql 
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'PAID';

You might add an index on both columns:

sql 
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

This can be much faster than scanning the entire table.

When Indexes Do Not Help Much

Indexes are not magic. They do not help in every scenario.

Common cases where indexes are less useful:

  1. Very small tables (for example 50 rows). A full table scan is often cheap.
  2. A column with very few distinct values, such as a boolean (is_deleted), especially if most rows share the same value.
  3. Queries that do not filter or sort by the indexed columns.
  4. Operations that need almost all rows anyway.

Example: Low Selectivity

Suppose you have:

sql 
CREATE TABLE logs (
    id         SERIAL PRIMARY KEY,
    level      VARCHAR(10) NOT NULL, -- 'INFO', 'WARN', 'ERROR'
    message    TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

If 95 percent of rows have level = 'INFO', the query

sql 
SELECT * FROM logs WHERE level = 'INFO';

might not benefit from an index on level. The database engine might decide it is faster to scan the table rather than use the index, because almost all rows match.

The concept here is selectivity. A highly selective column has many distinct values relative to the number of rows, so each value matches only a few rows. High selectivity is good for indexing.

How Indexes Affect Writes

Indexes speed up reads but slow down writes.

Whenever you:

the database must also update every index that involves those columns.

Example

If orders has these indexes:

sql 
CREATE INDEX idx_orders_user_id      ON orders (user_id);
CREATE INDEX idx_orders_status       ON orders (status);
CREATE INDEX idx_orders_user_status  ON orders (user_id, status);

And you run:

sql 
INSERT INTO orders (user_id, status, created_at)
VALUES (42, 'PAID', NOW());

The database will:

More indexes mean more work per write, which means slower inserts and updates.

Rule: Every index has a cost on INSERT, UPDATE, and DELETE.
Create indexes only when they solve a real query performance problem.

Types of Indexes (Conceptual Overview)

Different databases support different index types. As a beginner you mainly need to recognize these concepts.

Single Column Index

Indexes a single column.

sql 
CREATE INDEX idx_users_username ON users (username);

Useful for queries like:

sql 
SELECT * FROM users WHERE username = 'alice';

Composite (Multi-column) Index

Indexes multiple columns in a specific order.

sql 
CREATE INDEX idx_orders_user_status
    ON orders (user_id, status);

This index can help with:

sql 
SELECT * FROM orders
WHERE user_id = 42 AND status = 'PAID';

and often with:

sql 
SELECT * FROM orders
WHERE user_id = 42;

but not with:

sql 
SELECT * FROM orders
WHERE status = 'PAID';

This is because of how most B-tree composite indexes work: they are most useful if the query filters from left to right in the index definition.

A simple analogy:

Unique Index

Ensures no duplicate values for the indexed column or column combination.

Example, unique username:

sql 
CREATE UNIQUE INDEX idx_users_username_unique
    ON users (username);

Or a compound uniqueness, such as one email per user and provider:

sql 
CREATE UNIQUE INDEX idx_social_accounts_user_provider
    ON social_accounts (user_id, provider);

Now the pair (user_id, provider) must be unique.

Indexes for Sorting

Indexes can help with ORDER BY queries.

Example:

sql 
CREATE INDEX idx_orders_created_at
    ON orders (created_at);

This can make:

sql 
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20;

much faster, especially on large tables.

Many databases can use a single index for both filtering and ordering, for example:

sql 
CREATE INDEX idx_orders_user_created
    ON orders (user_id, created_at);

can help queries like:

sql 
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

Choosing Good Indexes for Queries

You should create indexes based on the queries your application actually runs.

Typical Workflow

  1. Write your queries.
  2. Run them on realistic data.
  3. Find slow queries using database tools.
  4. Inspect query plans (for example EXPLAIN).
  5. Add or adjust indexes for those queries.
  6. Test the performance again.

Example: Finding Orders for One User

Query:

sql 
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Good index:

sql 
CREATE INDEX idx_orders_user_created
    ON orders (user_id, created_at DESC);

Why:

Example: Enforcing Unique Emails and Fast Lookups

User table:

sql 
CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    email   VARCHAR(255) NOT NULL,
    ...
);

Requirement:

Use a unique index, typically via a constraint:

sql 
ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

This gives you both uniqueness and fast lookups.

Examples of Helpful vs Useless Indexes

Helpful Index Example

Query:

sql 
SELECT *
FROM posts
WHERE published = TRUE
  AND author_id = 10
ORDER BY created_at DESC
LIMIT 20;

Helpful index:

sql 
CREATE INDEX idx_posts_author_published_created
    ON posts (author_id, published, created_at DESC);

How it helps:

Useless or Redundant Index Example

Table:

sql 
CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    email    VARCHAR(255) UNIQUE,
    ...
);

Bad idea:

sql 
CREATE INDEX idx_users_email ON users (email);  -- Redundant

The UNIQUE constraint already created an index on email. The new index just doubles the write cost without benefit.

Another bad pattern:

sql 
CREATE INDEX idx_users_email      ON users (email);
CREATE INDEX idx_users_email_id   ON users (email, id);

The second index might be useful for a more specific query, but you should check carefully whether you actually need both.

Practical SQL Examples

Assume you have this schema:

sql 
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    available   BOOLEAN NOT NULL,
    created_at  TIMESTAMP NOT NULL
);

Scenario 1: Filter by Category and Availability

Frequent query:

sql 
SELECT *
FROM products
WHERE category_id = 5
  AND available = TRUE
ORDER BY created_at DESC
LIMIT 50;

Good index:

sql 
CREATE INDEX idx_products_category_available_created
    ON products (category_id, available, created_at DESC);

Reason:

Scenario 2: Search by Name Prefix

Query:

sql 
SELECT *
FROM products
WHERE name LIKE 'iphone%';

For basic LIKE 'prefix%' searches, a normal index on name can help:

sql 
CREATE INDEX idx_products_name ON products (name);

But note:

Scenario 3: Join by Foreign Key

Schema:

sql 
CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    category_id INT NOT NULL REFERENCES categories(id),
    ...
);

Query:

sql 
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'Electronics';

Indexes that help:

sql 
  CREATE INDEX idx_categories_name ON categories (name);
sql 
  CREATE INDEX idx_products_category_id ON products (category_id);

Measuring and Understanding Index Effects

You should verify that an index is actually used and helpful.

Although details vary by database, the usual steps are:

  1. Use a tool like EXPLAIN or EXPLAIN ANALYZE to see the query plan.
  2. Look for words like Index Scan or Index Seek in the plan, instead of Seq Scan or Table Scan.
  3. Compare execution times before and after creating the index.

Example in PostgreSQL:

sql 
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

You do not need to fully understand query plans yet, but you should know that they exist and are the main way to check whether indexes are doing their job.

Summary and Rules of Thumb

Indexes:

Common rules of thumb:

  • Index columns that you filter on frequently.
  • Index columns used for joins between tables.
  • Use composite indexes for common combinations of filters and sorting.
  • Do not create duplicate or unnecessary indexes.
  • Test performance changes before and after adding an index.

As you build more queries, you will come back to indexes often. They are one of the main levers for improving SQL performance, and understanding them early will make you a much better backend developer.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3156 / 24h
Impressum / Imprint
Datenschutz / Data Policy