Adding indexes is usually the first recommendation when a database query runs slowly. However, adding indexes blindly can actually degrade write performance while providing zero speedup for your SELECT queries.
To write high-performance queries, you must understand what happens inside the database engine storage engine at the disk block level.
The Anatomy of a B-Tree Index
Most relational databases (PostgreSQL, MySQL InnoDB, SQLite) default to B-Tree (Balanced Tree) indexes. A B-Tree maintains sorted data in logarithmic time:
[Root Node: 50 | 100]
/ | \
[Node: 10 | 30] [Node: 60 | 80] [Node: 120 | 150]
/ | \ / | \ / | \
[Leaf Nodes containing physical row pointer disk addresses]Every leaf node points to the physical disk page (`cTID` in Postgres, Clustered Primary Key in MySQL) where the remaining row columns reside.
The Leftmost Prefix Rule in Composite Indexes
When you create a composite index on multiple columns:
CREATE INDEX idx_leads_status_created ON leads (status, created_at);The database sorts entries first by status, and only secondarily by created_at.
The Cost of Over-Indexing
Every index is not free:
Audit your production databases regularly using query log analyzers and drop indexes with zero recorded scans.