Slow database queries are the primary cause of application latency, server CPU spikes, and failed user transactions. When web applications grow from hundreds of users to hundreds of thousands, brute-force full table scans bring databases to their knees. Mastering SQL indexing, query execution plan analysis, and database architecture is an indispensable skill for senior backend engineers.
1. How Database Indexes Work: The B-Tree Engine
Without an index, finding a single record in a table containing 1,000,000 rows requires the database engine to inspect every single page on disk from start to finish—an expensive Full Table Scan. A database index is an auxiliary data structure, typically structured as a balanced tree (B-Tree), that stores column values in sorted order alongside pointers to the actual row locations.
Searching a B-Tree index has logarithmic time complexity: O(log N). In a 1,000,000 row table, finding an exact match requires inspecting at most 3 to 4 tree levels rather than scanning 1,000,000 records, slashing disk I/O by 99.9%.
2. Dissecting Query Execution Plans with EXPLAIN ANALYZE
Never guess why a query is slow. Always inspect its execution plan using EXPLAIN or EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 4520 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;Look for key execution indicators:
type: ALL(orSeq Scanin PostgreSQL): Indicates a disastrous full table scan.type: reforrange: Indicates healthy index utilization.Using filesort/Using temporary: Indicates the database had to buffer data into memory or temporary disk files to sort results, severely degrading performance.
3. Composite Indexes and the "Leftmost Prefix Rule"
When queries filter or sort across multiple columns, creating individual single-column indexes on each field is inefficient. The database engine must evaluate which index to pick or attempt expensive index merges. Instead, create a tailored Composite (Multi-Column) Index:
-- Composite index covering filtering and sorting
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);The Leftmost Prefix Rule: A composite index on (A, B, C) can accelerate queries filtering on:
WHERE A = ?WHERE A = ? AND B = ?WHERE A = ? AND B = ? AND C = ?
However, it cannot be used for queries filtering only on WHERE B = ? or WHERE C = ? because the search tree is ordered starting from column A.
4. Index-Killing Anti-Patterns to Avoid
Developers frequently write queries that unintentionally disable database indexes:
| Anti-Pattern (Index Disabled) | Optimized Pattern (Index Used) | Reason |
|---|---|---|
WHERE YEAR(created_at) = 2026 | WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' | Applying functions to indexed columns prevents tree traversal |
WHERE name LIKE '%tech%' | Use Full-Text Indexing or Trigram Index | Leading wildcards prevent B-Tree prefix matching |
WHERE user_id = '1205' (when user_id is INT) | WHERE user_id = 1205 | Implicit datatype conversion forces row-by-row casting |
5. The Gold Standard: Covering Indexes
A Covering Index occurs when an index contains every single column requested by the SELECT, WHERE, and ORDER BY clauses. When a query is covered, the database engine retrieves all data directly from the index in RAM without ever touching the primary table pages on disk (indicated by Using index in MySQL):
-- Query:
SELECT customer_id, count(*), sum(total_amount)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;
-- Covering Index:
CREATE INDEX idx_orders_covering ON orders (status, customer_id, total_amount);Summary
Optimizing database performance is about aligning query structure with indexing strategy. By inspecting execution plans with EXPLAIN ANALYZE, crafting composite indexes following the leftmost prefix rule, and avoiding function wrapping, you transform sluggish multi-second queries into sub-millisecond operations.