How Databases Actually Work

Beyond "It Just Works"

You write Eloquent queries, run migrations, and your data appears. But when a page takes 3 seconds to load, or a query that worked fine with 100 rows suddenly crawls with 100,000 rows, you need to understand what's happening beneath the ORM.

How Data Is Stored: Pages and B-Trees

MySQL and PostgreSQL store data in fixed-size pages (typically 16KB in PostgreSQL, 16KB in InnoDB). A table with 1 million rows might occupy thousands of pages on disk.

Without an index, finding a specific row means reading every page sequentially — a full table scan. With 10,000 pages, that means 10,000 disk reads. This is why queries slow down as tables grow.

Indexes solve this using a data structure called a B-tree (balanced tree). A B-tree organizes index values in a sorted, hierarchical structure:

• The root node contains a few key values and pointers to child nodes.
• Each level divides the search space. With a branching factor of 100, three levels can index 1 million rows.
• The leaf nodes contain the actual indexed values and pointers to the corresponding table rows.

An index lookup traverses 3-4 levels of the B-tree (3-4 page reads) instead of scanning 10,000 pages. That's the difference between a 5ms query and a 5-second query.

The primary key of every InnoDB table IS a B-tree index. The table data itself is stored in the leaf nodes of the primary key index. This is called a "clustered index." Secondary indexes store their own B-tree with pointers back to the primary key.

The Query Planner

When you run a query, the database doesn't just execute it blindly. The query planner (also called the query optimizer) analyzes the query and chooses the most efficient execution strategy.

The planner considers:

• Which indexes are available and applicable.
• Table statistics (row counts, value distribution).
• The cost of different approaches (index scan vs. full scan vs. index-only scan).
• Join order when multiple tables are involved.

Sometimes the planner chooses a full table scan even when an index exists — if the query will return most of the rows anyway, sequential reading is faster than random B-tree lookups.

Reading EXPLAIN Output

The EXPLAIN command shows you the query planner's chosen strategy. In MySQL:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Key columns to watch:

• type — How the table is accessed. const or eq_ref means an index was used efficiently. ALL means full table scan — usually bad.
• key — Which index was used (if any). NULL means no index.
• rows — Estimated number of rows the engine needs to examine. Lower is better.
• Extra — Watch for "Using filesort" (sorting without an index) and "Using temporary" (temporary table created).

In Laravel, you can use DB::enableQueryLog() and DB::getQueryLog() to capture queries, or use Laravel Debugbar to see them in the browser.

Key Takeaways

• Data is stored in pages. Full table scans read every page; indexes enable targeted reads via B-trees.
• The query planner chooses the execution strategy. It doesn't always use indexes.
• EXPLAIN reveals the planner's strategy. Learn to spot ALL (full scan) and missing indexes.
• Performance problems are almost always about the number of rows examined vs the number of rows returned.