How Database Queries Really Work
Summary: A practical explanation of how database engines locate, filter, and return information. Rather than focusing on SQL syntax, this article explores what happens inside a database when a query is executed, from simple record scanning through to indexes, query planners, and modern optimisation techniques.
Context
The Natural Assumption
Why most people imagine databases simply reading every record.
The Sieve Analogy
Understanding queries as records passing through progressively finer filters.
Core Concepts
Records, Tables and Predicates
What a query is actually asking for.
The Full Table Scan
The simplest possible query engine.
Why Full Scans Become Expensive
CPU, memory and storage considerations.
Indexes: Moving the Sieve Closer to the Data
B-Tree Indexes
How databases avoid examining every record.
Hash Indexes
Optimised equality lookups.
Inverted Indexes
How text search works.
Bitmap Indexes
Filtering large datasets efficiently.
Query Planning
One Query, Many Possible Paths
Why databases must make decisions.
Selectivity
Finding the most restrictive filter first.
Predicate Pushdown
Applying filters as early as possible.
Cost-Based Optimisation
Estimating the cheapest route to an answer.
Storage Engine Considerations
Row-Oriented Storage
Column-Oriented Storage
Why Analytics Databases Feel Faster
Modern Optimisations
Block Elimination
Zone Maps
Bloom Filters
Vectorised Execution
Parallel Query Processing
Practical Application
Building Your Own Query Engine
A Sensible Evolution Path
- Full scan
- Indexed lookup
- Query planner
- Block skipping
- Vectorisation
Common Misconceptions
Databases Are Not Magic
SQL Is Not the Query Engine
Indexes Do Not Eliminate Filtering
Design & Architecture Considerations
Read Optimisation versus Write Optimisation
Choosing the Right Indexes
Storage Layout Matters
Conclusion
The Goal Is Not Faster Filtering
The Goal Is Filtering Less
