KAHIBARO
Discord Login Register ☰
☰

9.12. Indexes

Table of Contents

Why Indexes Matter

Indexes are one of the most important tools to make database queries fast.

Without indexes, the database usually has to scan every row in a table to answer many queries. This is called a full table scan and becomes very slow when you have thousands or millions of rows.

With an index, the database can quickly jump to the rows you need, similar to how you use the index at the back of a book to find a word without reading every page.

Key idea: Indexes speed up read queries, but they cost extra memory and slow down writes.
You should create indexes on purpose, not randomly.

In this chapter you will learn what indexes are, how they work conceptually, when to create them, and common patterns and pitfalls, with concrete SQL examples.

What Is an Index?

An index is an additional data structure that a database maintains to help find rows quickly.

Imagine a table users:

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

If you run:

sql 
SELECT * FROM users WHERE email = 'alice@example.com';

Without an index on email, the database:

  1. Starts at the first row.
  2. Checks if email = 'alice@example.com'.
  3. Moves to the next row.
  4. Repeats until the end.

With an index on email:

sql 
CREATE INDEX idx_users_email ON users (email);

The database uses the index to directly locate the row(s) with that email, usually in logarithmic time instead of linear time.

Informally:

The index does not replace the table. It is an extra structure stored on disk (and partly cached in memory) that references table rows.

How Indexes Work Conceptually

Different databases and index types use different internal structures. The most common for general indexing is the B-tree (balanced tree).

You do not need to know tree algorithms in detail, but the idea is:

For example, if you index email:

emailrow pointer
alice@example.comrow #17
bob@example.orgrow #328
charlie@example.netrow #215

To find bob@example.org, the database searches inside the tree structure, not the whole table.

You cannot see or change the internal structure of standard indexes.
You interact with indexes only by defining them and letting the database choose how to use them.

Basic Index Syntax

Most SQL databases use similar syntax for creating and dropping indexes.

Creating a simple index

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 = 'alice@example.com';

The database query planner decides when to use the index. You do not call the index directly.

Dropping an index

If an index is not useful or is hurting performance (for writes), you can drop it:

sql 
DROP INDEX idx_users_email;

The table and its data remain. Only the index is removed.

Be careful when dropping indexes in production. Some queries might become very slow if they depended on that index.

Unique indexes

A unique index enforces that the indexed column combination has no duplicates.

Syntax often looks like:

sql 
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

or by using a constraint when creating the table:

sql 
CREATE TABLE users (
    id     SERIAL PRIMARY KEY,
    email  TEXT NOT NULL UNIQUE
);

Unique indexes are especially useful for things like:

They both speed up lookups and enforce data correctness.

When Indexes Help

Indexes help mainly when your query:

  1. Filters rows with a WHERE condition.
  2. Joins tables with JOIN ... ON ....
  3. Orders results with ORDER BY.
  4. Enforces uniqueness.

Speeding up WHERE conditions

If you frequently search by a column, an index can help.

Example table:

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

Common query:

sql 
SELECT * FROM orders WHERE user_id = 42;

Index:

sql 
CREATE INDEX idx_orders_user_id ON orders (user_id);

Now, looking up all orders for a user can be very fast.

Another example, filtering by status:

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

If this query is common, you might create:

sql 
CREATE INDEX idx_orders_status ON orders (status);

Speeding up joins

Joins usually compare foreign keys to primary keys or unique columns. Indexes on those columns are critical.

Example:

sql 
CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    ...
);
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INT NOT NULL REFERENCES users(id),
    ...
);

Query:

sql 
SELECT *
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 42;

Indexes that help:

sql 
CREATE INDEX idx_orders_user_id ON orders (user_id);

Without this index, the database may have to scan many orders for each user during joins.

Speeding up ORDER BY

If you sort by a column, an index on that column can let the database read rows already in sorted order.

Example:

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

Helpful index:

sql 
CREATE INDEX idx_orders_created_at ON orders (created_at);

Now the database can quickly find the latest 20 orders by traversing the index in reverse order, instead of sorting all rows.

Speeding up combined filtering and sorting

Indexes are especially powerful when the same index supports both filtering and sorting.

Example:

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

Index:

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

This is a multi-column index. It is sorted first by user_id, then by created_at within each user_id. The database can:

  1. Jump to the section where user_id = 42.
  2. Read rows already ordered by created_at.

This can avoid both a full scan and a sort step.

Multi-Column Indexes

A multi-column index covers more than one column.

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

This index is particularly useful for queries that filter on user_id first, then status, such as:

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

Index column order matters

The order of columns in a multi-column index is critical.

For the index:

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

Most database planners can use it efficiently when your WHERE clauses use:

But often not when you do:

In many systems, the index is organized by the first column first. If your query does not filter by the leading column, the index is less useful.

Consider two indexes:

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

Which one is better depends on your most common queries.

You rarely need both, because each index has a cost.

Covering queries

A covering index is an index that contains all the columns a query needs, so the database does not need to read the main table rows at all.

Example:

sql 
CREATE INDEX idx_orders_user_created_status
ON orders (user_id, created_at, status);

Query:

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

All three columns are in the index. The database can execute the query just using the index. This can be very fast.

However, adding many columns to indexes increases index size and maintenance cost, so do it only for important queries.

Indexes and Primary / Foreign Keys

Primary keys and many unique constraints create indexes automatically.

Primary keys

When you declare a primary key:

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

The database typically creates a unique index on id behind the scenes. You do not need to create another one on the same column.

You will almost always see:

Foreign keys

A foreign key constraint does not always automatically create an index. Many databases recommend you manually index foreign key columns.

Example:

sql 
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INT NOT NULL REFERENCES users(id),
    ...
);

Add an index:

sql 
CREATE INDEX idx_orders_user_id ON orders (user_id);

This helps for:

Rule of thumb:
Every foreign key column should usually have an index to avoid slow joins and slow deletes/updates.

Costs and Trade-offs of Indexes

Indexes are not free. You must understand their costs.

Extra storage

Each index is stored on disk. If your table has millions of rows and many indexes, index files can become large.

Rough idea:

Slower writes

Whenever you:

the database must also update all relevant indexes.

If a table has many indexes, inserts and updates become slower, because every index must be adjusted.

Example:

sql 
INSERT INTO users (email, username, created_at)
VALUES ('new@example.com', 'newuser', NOW());

If you have indexes on email, username, and created_at, the database must:

  1. Insert the row into the table.
  2. Insert the new email into the email index.
  3. Insert the new username into the username index.
  4. Insert the new created_at into the created_at index.

This is still fast but slower than without indexes. On write-heavy systems, too many indexes can hurt overall performance.

Maintenance complexity

As your application grows, you may need to:

You should keep track of why each index exists. Many teams document important indexes next to their creation migrations.

Choosing What to Index

You should not index every column. Instead, decide based on how the application queries the data.

Good candidates for indexes

Typical good candidates:

  1. Primary keys (already indexed).
  2. Foreign keys (you usually add indexes).
  3. Columns used often in WHERE conditions.
  4. Columns used in JOIN conditions.
  5. Columns used frequently in ORDER BY.
  6. Columns used frequently in GROUP BY.

Example decisions:

sql 
  SELECT * FROM users WHERE email = ?;

Index email.

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

Index (user_id, created_at) as a multi-column index.

Columns that usually should not be indexed

Some columns are often bad index candidates:

Example:

sql 
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL,
    description TEXT
);

Indexing is_active alone is usually not very helpful:

sql 
CREATE INDEX idx_products_is_active ON products (is_active);

If almost all products are active, the database still has to look at many rows, so the index will not improve much. It may even be slower because it has to read the index and then many table rows.

However, is_active might be useful as part of a multi-column index, for example:

sql 
CREATE INDEX idx_products_is_active_name
ON products (is_active, name);

if you frequently query:

sql 
SELECT * FROM products
WHERE is_active = TRUE
ORDER BY name
LIMIT 50;

Using query patterns

Look at your most common queries and design indexes for them. For example, imagine these frequent queries:

sql 
-- 1. Find user by email
SELECT * FROM users WHERE email = ?;
-- 2. Recent orders of a user
SELECT * FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;
-- 3. Admin list of paid orders, newest first
SELECT * FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC
LIMIT 100;

Good index plan:

sql 
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

Using EXPLAIN to See Index Usage

Most databases provide an EXPLAIN command that shows how a query will be executed. This is very useful to check if your indexes are being used.

Example (syntax varies slightly by database, here is a generic style):

sql 
EXPLAIN
SELECT * FROM users
WHERE email = 'alice@example.com';

A simplified result might say something like:

If you see sequential scan on a big table for an important query, you likely need a better index.

You do not need to memorize EXPLAIN output, but you should know that:

Indexing Patterns and Examples

Here are some common scenarios and example indexes you can use.

Pattern 1: Login by username or email

Table:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    username    TEXT NOT NULL UNIQUE,
    password    TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL
);

Queries:

sql 
SELECT * FROM users WHERE email = ?;
SELECT * FROM users WHERE username = ?;

Indexes:

Both queries become fast.

Pattern 2: Activity feed for a user

Table:

sql 
CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    user_id    INT NOT NULL,
    body       TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

Query:

sql 
SELECT * FROM posts
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

Index:

sql 
CREATE INDEX idx_posts_user_created
ON posts (user_id, created_at DESC);

This index covers exactly the WHERE and ORDER BY, and the database can usually avoid extra sorting.

Pattern 3: Search by prefix

Simple B-tree indexes can help with prefix matches like WHERE name LIKE 'Ali%'.

Table:

sql 
CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL
);

Query:

sql 
SELECT * FROM customers
WHERE name LIKE 'Ali%';

Index:

sql 
CREATE INDEX idx_customers_name ON customers (name);

Because the prefix is fixed at the start of the string, the index can be used.
But for a pattern like '%ali%', a normal index is not very helpful, you may need full-text or special indexes, which belong to more advanced topics.

Pattern 4: Soft deletes

Many applications have a column deleted_at or is_deleted to implement soft deletes.

Table:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    deleted_at  TIMESTAMP NULL
);

Common queries:

sql 
SELECT * FROM users
WHERE email = ? AND deleted_at IS NULL;

Index:

sql 
CREATE INDEX idx_users_email_not_deleted
ON users (email)
WHERE deleted_at IS NULL;  -- partial index (not available in every DB)

A partial index only includes rows that match the condition. It can be smaller and faster and is especially useful when most rows are deleted or inactive.
Partial indexes are database-specific features, so details may differ, but the idea is the same.

Common Mistakes With Indexes

Beginners often make these mistakes:

1. Indexing everything

Better approach:

2. Ignoring multi-column indexes

sql 
  CREATE INDEX idx_orders_user_id ON orders (user_id);
  CREATE INDEX idx_orders_created_at ON orders (created_at);
sql 
  SELECT * FROM orders
  WHERE user_id = ?
  ORDER BY created_at DESC;

In many databases, the separate indexes will not help as much as a combined one:

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

3. Wrong column order

Using an index (status, user_id) while most queries filter WHERE user_id = ? and rarely filter or sort by status.

Correct order:

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

4. Not indexing foreign keys

Forgetting to index user_id in orders:

sql 
CREATE TABLE orders (
    id       SERIAL PRIMARY KEY,
    user_id  INT NOT NULL REFERENCES users(id),
    ...
);

Without the index, queries and deletes involving user_id can be slow.

5. Expecting indexes to fix every performance problem

Indexes only help for certain patterns. They cannot fix:

Indexes are one tool, not magic.

Summary

Indexes are essential for fast database queries in backend development. You learned that:

With this understanding, you can start making intentional indexing decisions in your backend applications and be better prepared for deeper database tuning topics.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

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