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.

Practical Application

A Sensible Evolution Path

• Full scan
• Indexed lookup
• Query planner
• Block skipping
• Vectorisation

Conclusion

The Goal Is Not Faster Filtering

The Goal Is Filtering Less