Optimize Queries with Database Indexing

Overview

Database indexes are data structures that speed up read operations by providing fast pathways to rows without scanning entire tables. Without proper indexes, even simple WHERE clauses force the database to examine every row sequentially — a full table scan that becomes unbearably slow as data grows.

However, indexes are not free. Every write (INSERT, UPDATE, DELETE) must update all relevant indexes, and each index consumes disk space and memory. The goal is to create the right indexes for your read patterns while minimizing overhead on writes.

When to Use

Use this recipe when:

• Queries are slowing down as table size grows
• Analyzing slow query logs or execution plans reveals sequential scans
• Adding pagination or search filters to an existing table
• Designing a new schema and predicting access patterns
• Troubleshooting lock contention caused by long-running reads

Solution

Basic Index (Single Column)

-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- Query now uses the index instead of scanning the entire table
SELECT * FROM users WHERE email = 'alice@example.com';

Composite Index (Multiple Columns)

-- Column order matters: equality filters first, range filters second
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- Supports:
-- WHERE user_id = 1
-- WHERE user_id = 1 AND created_at > '2025-01-01'
-- ORDER BY user_id, created_at DESC

Partial Index

-- Only index active users — smaller and faster for this specific query
CREATE INDEX idx_active_users_email
ON users(email)
WHERE active = true;

Analyzing Query Plans

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

Look for:

• Seq Scan = sequential table scan (slow on large tables, needs an index)
• Index Scan or Index Only Scan = using an index (fast)
• Bitmap Heap Scan = using multiple indexes or a partial match

Explanation

• B-tree indexes: The default index type. Excellent for equality and range queries (=, <, >, BETWEEN). Most databases use B-tree for primary keys automatically.
• Composite indexes: The database can use the index for any prefix of the column list. An index on (a, b, c) supports queries on (a), (a, b), and (a, b, c), but not (b) or (c) alone.
• Covering indexes: If all columns a query needs are in the index, the database can answer the query without touching the table. This is called an “index-only scan” and is dramatically faster.
• Partial indexes: Smaller indexes that only cover a subset of rows. Useful for tables where most queries filter on a specific condition (e. g. , active = true).

Variants

Index Type	Best For	Trade-off
B-tree	Equality, range, ordering	General purpose, higher write cost
Hash	Exact equality only	Faster lookups, no range support
GiST / GIN	Full-text search, JSON, arrays	Larger, slower to build
BRIN	Very large, naturally ordered tables	Tiny size, approximate results

What Works

• Index the columns in your WHERE clause: if a query filters on user_id and status, an index on (user_id, status) is the first thing to try.
• Put equality columns before range columns: in (a, b) where a = 1 and b > 100, the index on (a, b) is far more useful than (b, a).
• Avoid indexing low-cardinality columns alone: a status column with only 3 values (active, pending, archived) does not benefit from a standalone index. Combine it with a high-cardinality column.
• Remove unused indexes: every index slows down writes.
• Index foreign key columns: databases do not always auto-index foreign keys. Missing indexes on JOIN columns cause expensive nested loop scans. See database design. See SQL Joins for join optimization.

Common Mistakes

• Indexing every column: this wastes disk space, slows writes dramatically, and confuses the query optimizer with too many choices.
• Wrong column order in composite indexes: an index on (created_at, user_id) cannot help a query that filters only on user_id.
• Indexing columns that are never queried: check your query logs before creating indexes.
• Ignoring index maintenance: fragmented indexes on high-churn tables degrade over time. See SQL performance tuning.
• Using indexes on tiny tables: tables with fewer than a few thousand rows are often faster with sequential scans because reading the index and then the table is more overhead than a full scan.

Troubleshooting

• Largest Contentful Paint is high: optimize images, preload critical resources, and reduce server response time.
• JavaScript bundle size grows: analyze the bundle, split code by route, and tree-shake unused dependencies. Lazy-load non-critical components.
• Cache hit rate is low: review cache keys, TTLs, and invalidation patterns.
• Database CPU spikes: find the top queries by execution time and frequency. Add indexes, rewrite queries, or cache results.
• Throughput drops under load: profile for contention, garbage collection, and blocked threads. Scale horizontally only after optimizing the hot path.

Further Reading

• Official documentation: check the current reference for the framework or tool used.
• Related guides: explore the performance and database guides for deeper coverage.
• Complementary patterns: review design patterns applicable to your technology stack.
• Public postmortems: study real incidents from teams that faced similar production issues.

Production Notes

• Deploy gradually using canary or blue-green to catch regressions early.
• Configure alerts for error rate, p99 latency, and failure rate before enabling in production.
• Document the rollback in the runbook; test the procedure in staging at least once per quarter.
• Review structured logs with correlation IDs to trace requests end-to-end during incidents.

Key Takeaways

• Apply optimize queries with database indexing when you need a practical solution for your use case.
• Monitor performance after implementation; measure latency, errors, and resource usage before and after.
• Check the Troubleshooting section for common failures; most have documented root causes with fixes.
• Keep dependencies updated and run tests in CI to prevent production regressions.

See Also

• Batch Processing Patterns — optimizing bulk data operations
• Web Performance — frontend and backend performance techniques
• Load Testing — validating database performance under load

Common Production Pitfalls

• Copying the example without adapting it to real data volumes and failure modes.
• Skipping load and error-injection tests before the first production deployment.
• Hard-coding values that should be configurable per environment.
• Forgetting to add logging and monitoring at each step.
• Deploying without a rollback plan or a tested backup strategy.
• Assuming the minimal example will scale without adding caching or batching.
• Not documenting the version and configuration used in production.
• Letting the recipe sit unchanged when dependencies or scale evolve.