DEV Community

tanmay kuamr chaurasia
tanmay kuamr chaurasia

Posted on

Database Indexing Basics That Will Save You From Slow SQL Queries

Your database might not actually be slow—your queries might just be lost.

It is a common scenario in backend engineering: as your application grows and your database tables expand from thousands to millions of rows, once-lightning-fast APIs start timing out. Developers often jump to upgrading database hardware or adding caching layers like Redis, when the real culprit is simple: missing database indexes.

When a query runs without an index, the database engine has no choice but to perform a full table scan. That means it reads every single row on disk from start to finish just to return a handful of matching records.

Here is how database indexing works, how to spot missing indexes, and how to fix slow queries step by step.

The Mental Model: Table of Contents vs. Reading Cover-to-Cover

Think of a database table as a 1,000-page technical manual. If you want to find every mention of "B-Tree indexes" without an index, you have to read all 1,000 pages line by line.

A database index works like the index at the back of that manual. It maintains an organized, sorted data structure (typically a B-Tree) that maps column values directly to their physical location on disk.

Instead of scanning millions of records sequentially, the database engine uses this lookup structure to jump directly to the exact target rows in milliseconds.

Step-by-Step: Fixing a Slow Query

Imagine an e-commerce backend with an orders table containing millions of records. Users frequently query their order history filtered by user_id and sorted by created_at.

Here is the basic query:

SELECT id, total_amount, status, created_at
FROM orders
WHERE user_id = 42891
ORDER BY created_at DESC;
Enter fullscreen mode Exit fullscreen mode

Without an index on user_id, the database scans every single row in the orders table.

To inspect how the database engine plans to execute this query, run an EXPLAIN ANALYZE statement:

EXPLAIN ANALYZE
SELECT id, total_amount, status, created_at
FROM orders
WHERE user_id = 42891
ORDER BY created_at DESC;
Enter fullscreen mode Exit fullscreen mode

If the execution plan outputs Seq Scan (Sequential Scan) in PostgreSQL or ALL in MySQL, it means the database is scanning the entire table.

To fix this, create a composite index that covers both the filtering column (user_id) and the sorting column (created_at):

CREATE INDEX idx_orders_user_created 
ON orders (user_id, created_at DESC);
Enter fullscreen mode Exit fullscreen mode

After adding this index, re-running the same EXPLAIN ANALYZE command will show an Index Scan. The database engine skips the full table read entirely. The execution time can plummet from several seconds down to single-digit milliseconds on large datasets.

Where Should You Add Indexes?

You do not need to index every column in your database schema. Focus on columns that frequently appear in:

  • WHERE clauses: Fields used repeatedly for filtering (e.g., user_id, email, status).
  • JOIN conditions: Foreign key columns connecting relational tables (e.g., account_id, product_id).
  • ORDER BY clauses: Columns used for sorting query results (e.g., created_at, updated_at).

High-cardinality columns—columns with a wide variety of unique values like UUIDs or timestamps—benefit the most from indexing. Low-cardinality columns like boolean flags (is_active) rarely benefit from single-column indexes because the database still has to scan a large portion of the table.

The Hidden Cost: Indexes Aren't Free

Indexes improve read performance significantly, but they come with a trade-off.

Every time you execute an INSERT, UPDATE, or DELETE query, the database must write the change to the table and update every associated index.

If you put an index on every column:

  • Write performance drops: Data modification queries take longer because multiple index trees must be re-balanced.
  • Memory overhead increases: Indexes need to fit into RAM for optimal fast lookup performance.
  • Disk storage grows: Large indexes can match or exceed the size of the underlying table itself.

The goal isn't to index everything, but to identify the specific high-impact read paths in your application and support them with targeted indexes.

About me

Tanmay builds AI agents and automation for businesses and is open to freelance projects. Feel free to connect with him on LinkedIn.

Top comments (0)