DevOps

Database Indexing Strategy: A Complete Guide to Faster Queries

2026-07-19·13 min read
#database#indexing#PostgreSQL#MySQL#performance

Database Indexing Strategy: A Complete Guide to Faster Queries

Database indexing is the single most impactful thing you can do for application performance. A well-placed index can turn a 30-second query into a 3-millisecond query. A poorly placed index can slow down writes, waste disk space, and do nothing for your reads.

This guide covers everything: how indexes work, which type to use, how to find missing indexes, and — just as importantly — when NOT to index.

What Is a Database Index?

An index is a separate data structure that lets the database find rows without scanning the entire table. Think of a book's index: instead of flipping through every page to find a topic, you look up the topic in the index, get a page number, and jump directly there.

Without an index, PostgreSQL and MySQL perform a Sequential Scan (Seq Scan) — reading every row in the table. With a proper index, they perform an Index Scan — reading only the relevant rows.

For a table with 10 million rows, the difference is:

Seq Scan:  read 10,000,000 rows  (~5000ms)
Index Scan: read ~50 rows        (~3ms)

That's a 1000x improvement from one index.

How Indexes Work: B-Trees

The default index type in both PostgreSQL and MySQL (InnoDB) is a B-tree (balanced tree). Here's what happens when you create an index on a column:

CREATE INDEX idx_users_email ON users(email);

The database builds a balanced tree structure where:

  • Each node contains a sorted subset of email values
  • Leaf nodes contain pointers to actual table rows
  • The tree is kept balanced — all leaf nodes are at the same depth

This structure enables O(log n) lookups instead of O(n) sequential scans.

Types of Indexes

1. B-Tree Index (Default)

Best for: equality checks (=), range queries (<, >, BETWEEN), and prefix matching (LIKE 'abc%').

-- PostgreSQL / MySQL
CREATE INDEX idx_products_price ON products(price);

-- Used by:
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;
SELECT * FROM products WHERE name LIKE 'Lap%';

2. Composite (Multi-Column) Index

Best for: queries that filter on multiple columns.

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Used by all of these:
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

The cardinality rule matters: put the most selective column first, or more precisely, the column that appears in the most queries. The index is usable from left to right — WHERE status = 'shipped' alone cannot use the index above efficiently.

3. Partial Index (PostgreSQL)

Best for: indexing a subset of rows that you query frequently.

-- Only index active users (saves space and is faster)
CREATE INDEX idx_active_users ON users(last_login_at)
WHERE active = true;

-- Used by:
SELECT * FROM users WHERE active = true AND last_login_at > '2026-01-01';

If 90% of your users are inactive, this index is 10x smaller than a full index — and proportionally faster to scan.

4. Covering Index

A covering index includes all columns the query needs, so the database never touches the table itself:

-- PostgreSQL: INCLUDE clause
CREATE INDEX idx_products_covering ON products(category_id)
INCLUDE (name, price);

-- MySQL: store in the index
CREATE INDEX idx_products_covering ON products(category_id, name, price);

-- Now this query is served entirely from the index:
SELECT name, price FROM products WHERE category_id = 5;

This turns an Index Scan into an Index-Only Scan — the fastest possible read.

5. Hash Index

Best for: simple equality checks only.

-- PostgreSQL
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

-- Only used by:
SELECT * FROM sessions WHERE session_token = 'abc123';
-- NOT usable for ranges, sorting, or pattern matching

6. GIN and GiST (PostgreSQL)

Best for: full-text search, JSONB, array operations.

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', body));

-- JSONB queries
CREATE INDEX idx_events_data ON events USING GIN(metadata);

-- Used by:
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('database');
SELECT * FROM events WHERE metadata @> '{"type": "click"}';

Finding Missing Indexes

PostgreSQL: pg_stat_statements

Enable the extension and find slow queries:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by total time
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

EXPLAIN ANALYZE

Always use EXPLAIN ANALYZE to understand query execution:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'shipped';

-- If you see "Seq Scan on orders", you're missing an index
-- If you see "Index Scan using idx_orders_user_status", you're good

Key things to look for in EXPLAIN output:

| Indicator | Meaning | |-----------|---------| | Seq Scan | Full table scan — likely needs an index | | Index Scan | Index is being used ✓ | | Index Only Scan | Covering index — optimal ✓ | | Bitmap Heap Scan | Index used, then rows fetched | | Sort | Sorting in memory — may need an index for ORDER BY | | Nested Loop | Join strategy — check if join columns are indexed |

MySQL: Slow Query Log

-- Enable slow query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- Log queries slower than 500ms

-- Then use EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

Look at the type column: ALL means full scan (bad), ref or range means index is used (good).

Index Anti-Patterns

1. Over-Indexing

Every index slows down writes (INSERT, UPDATE, DELETE) because the index must be updated too. Rule of thumb:

  • OLTP: 3–5 indexes per table, max
  • OLAP: More indexes are acceptable (read-heavy)
-- Check index sizes in PostgreSQL
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

If an index is larger than the table itself, question its value.

2. Unused Indexes

-- PostgreSQL: find indexes that were never used
SELECT *
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Never used since last reset
  AND schemaname = 'public';

Drop them:

DROP INDEX idx_never_used;

3. Low-Cardinality Indexes

Indexing a column with few unique values (like gender, active, status with 2–3 values) is rarely useful. The B-tree can't eliminate enough rows to justify the traversal.

Exception: partial indexes on low-cardinality columns can work well:

CREATE INDEX idx_pending_orders ON orders(id) WHERE status = 'pending';

4. Function Calls on Indexed Columns

-- WRONG: index on created_at is NOT used
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-19';

-- RIGHT: index is used
SELECT * FROM orders
WHERE created_at >= '2026-07-19' AND created_at < '2026-07-20';

If you must use functions, create a functional index:

CREATE INDEX idx_orders_date ON orders (DATE(created_at));

Index Maintenance

Rebuilding Fragmented Indexes

Over time, indexes become fragmented from inserts and deletes.

PostgreSQL:

-- Check bloat
SELECT schemaname, tablename, indexname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild (locks the table)
REINDEX INDEX idx_users_email;

-- Rebuild concurrently (no lock, but slower)
REINDEX INDEX CONCURRENTLY idx_users_email;

MySQL:

ANALYZE TABLE orders;  -- Update index statistics
OPTIMIZE TABLE orders; -- Rebuild table + indexes

Updating Statistics

The query planner needs accurate statistics to choose the right index:

-- PostgreSQL
ANALYZE users;  -- Update statistics for one table
VACUUM ANALYZE;  -- Clean dead tuples + update stats for all tables

-- MySQL
ANALYZE TABLE users;

Real-World Example: Optimizing an E-Commerce Query

Before (slow):

SELECT name, price, image_url
FROM products
WHERE category_id = 5
  AND price BETWEEN 10 AND 100
  AND active = true
ORDER BY price ASC
LIMIT 20;

EXPLAIN shows: Seq Scan on products — reading all 2 million rows. Execution: 4,200ms.

Step 1: Add a composite index

CREATE INDEX idx_products_category_price
ON products(category_id, price)
WHERE active = true;

Step 2: Make it covering

DROP INDEX idx_products_category_price;

CREATE INDEX idx_products_category_price
ON products(category_id, price)
INCLUDE (name, image_url)
WHERE active = true;

After:

EXPLAIN shows: Index Only Scan — reading only 20 rows. Execution: 2ms.

That's a 2,100x speedup from one index.

Checklist: Index Strategy Audit

Run through this for every production table:

  1. Does the table have a primary key? (clustered index)
  2. Are foreign key columns indexed? (join performance)
  3. Are common WHERE clauses covered?
  4. Are common ORDER BY/GROUP BY columns indexed?
  5. Are there unused indexes that can be dropped?
  6. Are indexes fragmented? (schedule maintenance)
  7. Are statistics up to date?
  8. Are slow queries using indexes? (check EXPLAIN)

Conclusion

Database indexing isn't optional — it's the difference between an application that scales and one that falls over at 10,000 users. The principles are simple:

  1. Index what you query
  2. Use composite indexes for multi-column filters
  3. Use covering indexes for hot queries
  4. Remove what you don't use
  5. Measure with EXPLAIN ANALYZE, not guess

Get this right, and your database will handle 100x the traffic on the same hardware.