Executive Summary: Proven techniques for reducing MySQL and PostgreSQL query execution time from 4.2 seconds down to 12ms across tables with millions of rows.
When scaling relational databases past millions of records, standard SELECT queries without proper indexing can lock tables and cause severe server CPU spikes.
Before attempting to optimize a slow query, run EXPLAIN ANALYZE in MySQL 8 or PostgreSQL to inspect the execution plan:
Type ALL (Full Table Scan): Indicates the database is checking every single row sequentially. This must be eliminated for high-volume tables.
Type ref or range: Indicates the query is leveraging an index efficiently.
When creating multi-column composite indexes, column order is paramount. Place the columns used in equality checks first, followed by range checks (>, <, BETWEEN), and finally columns used in ORDER BY:
A covering index includes all requested SELECT columns within the index B-Tree itself, eliminating the need for the database engine to perform secondary primary key lookups in table storage (Bookmark Lookup).
For massive historical tables (e.g. audit logs, biometric check-ins), implement range partitioning by date (YEAR(created_at), MONTH(created_at)). This allows query pruning to scan only the active partition while archiving older partitions to cold storage.
1. Understanding EXPLAIN & Query Execution Plans
Before attempting to optimize a slow query, run EXPLAIN ANALYZE in MySQL 8 or PostgreSQL to inspect the execution plan:
2. Composite Index Ordering Rule (Leftmost Prefix)
When creating multi-column composite indexes, column order is paramount. Place the columns used in equality checks first, followed by range checks (>, <, BETWEEN), and finally columns used in ORDER BY:
-- Optimal index for: WHERE tenant_id = 5 AND status = 'active' ORDER BY created_at DESC
CREATE INDEX idx_tenant_status_created ON orders (tenant_id, status, created_at DESC);
3. Covering Indexes
A covering index includes all requested SELECT columns within the index B-Tree itself, eliminating the need for the database engine to perform secondary primary key lookups in table storage (Bookmark Lookup).
4. Partitioning & Archiving Strategy
For massive historical tables (e.g. audit logs, biometric check-ins), implement range partitioning by date (YEAR(created_at), MONTH(created_at)). This allows query pruning to scan only the active partition while archiving older partitions to cold storage.