Dex: Created page with "'''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 querie..."

2026-07-05T14:09:45Z

Created page with "'''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 querie..."

New page

'''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

How Database Queries Really Work - Revision history