How Do SQL Database Engines Work?

Introduction

Imagine you own a giant library. Not a small bookshelf — a warehouse with millions
of books. Someone walks up to the front desk and asks:

"Bring me the three highest-rated cookbooks published after 2015, sorted by rating."

You could walk every aisle, open every book, and check each one. That would take
days. Instead, a good librarian uses a catalog, knows exactly which shelves to
visit, and hands you the answer in seconds.

A SQL database engine is that librarian. You give it a sentence in plain-ish
English (SQL), and it figures out — all by itself — the fastest way to find your
answer inside a mountain of data. This post explains how it does that, one step at
a time, with real queries you can try yourself.

What is SQL, really?

SQL (Structured Query Language) is special because you describe what you want,
not how to get it. You never write "loop over every row and compare." You just
say:

SELECT title, rating
FROM cookbooks
WHERE year > 2015
ORDER BY rating DESC
LIMIT 3;

The engine's whole job is to turn that description of the result into an actual
plan of action. Everything below is about that translation.

The journey of a single query

When you press Enter, your query passes through a small assembly line inside the
engine:

   Your SQL text
        |
        v
   +----------+     +-----------+     +-----------+     +-----------+
   |  PARSER  | --> | OPTIMIZER | --> | EXECUTOR  | --> |  STORAGE  |
   +----------+     +-----------+     +-----------+     +-----------+
   "Is this      "What's the      "Do the steps    "Read/write the
    valid SQL?"   fastest plan?"    in order."       actual bytes."

Let's walk each station.

1. The Parser — "Do I even understand you?"

The parser reads your text and checks the grammar, exactly like a teacher checking a
sentence. Did you spell SELECT right? Does the table exist? Are the parentheses
balanced?

If you write:

SELCT * FROM cookbooks;

the engine stops immediately:

ERROR 1064 (42000): You have an error in your SQL syntax near 'SELCT * FROM cookbooks'

No data was touched. The parser turns valid SQL into an internal tree structure (think
of it as a diagram of your sentence) that the rest of the engine can work with.

2. The Optimizer — "What's the smartest way?"

This is the clever part. There is almost always more than one way to get the same
answer, and some ways are thousands of times faster than others. The optimizer
estimates the cost of each plan and picks the cheapest.

Say you join two tables — shows and their ratings:

SELECT tv_shows.title, SUM(tv_show_ratings.rate) AS rating
FROM tv_shows
JOIN tv_show_ratings ON tv_shows.id = tv_show_ratings.show_id
GROUP BY tv_shows.title
ORDER BY rating DESC;

Should the engine start from tv_shows and look up ratings, or start from
tv_show_ratings and look up shows? Should it use an index or scan everything? You
don't decide — the optimizer does. You can even peek at its decision with EXPLAIN:

EXPLAIN SELECT * FROM cookbooks WHERE year > 2015;

+----+-------------+-----------+-------+---------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key     | rows | ...  | Extra       |
+----+-------------+-----------+-------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | cookbooks | range | idx_year      | idx_year|   42 | ...  | Using where |
+----+-------------+-----------+-------+---------------+---------+------+------+-------------+

That rows: 42 means the engine expects to examine only ~42 rows instead of the whole
million — because it found a helpful index. More on indexes in a moment.

3. The Executor — "Now actually do it."

Once a plan is chosen, the executor runs the steps in order: open the tables, fetch
rows, apply the WHERE filter, group them, sort them, and hand back the final rows.
It's the worker following the recipe the optimizer wrote.

4. The Storage Engine — "Where the bytes live."

Underneath everything sits the storage engine (in MySQL, usually InnoDB). It is
responsible for the real, physical reading and writing of data on disk, plus caching
hot data in memory so repeat questions are fast. MySQL can even use different storage
engines for different tables:

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = 'my_library';

+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| cookbooks  | InnoDB |
| authors    | InnoDB |
+------------+--------+

Indexes: the library catalog

Here is the single most important idea for making databases fast.

Without an index, finding WHERE year > 2015 means checking every book — a "full
table scan." With an index, the engine keeps a pre-sorted lookup structure (a
B-tree) that points straight to the right rows, exactly like the alphabetical
catalog at a library.

Full scan (no index)         B-tree index on "year"
--------------------         -----------------------
row 1  -> check                     [2015]
row 2  -> check                    /      \
row 3  -> check              [2010]        [2020]
 ...  (1,000,000 checks)     /    \        /    \
                          ...    ...    ...    (jump straight here)

You create one like this:

CREATE INDEX idx_year ON cookbooks (year);

Watch the difference the catalog makes:

-- Before the index: examines every row
EXPLAIN SELECT * FROM cookbooks WHERE year > 2015;   -- rows: 1000000

-- After CREATE INDEX idx_year: jumps to the right range
EXPLAIN SELECT * FROM cookbooks WHERE year > 2015;   -- rows: 42

The trade-off: indexes make reads fast but make writes slightly slower (every INSERT
must also update the catalog), and they use extra disk space. So you index the columns
you search and sort by, not every column.

Joins: answering questions that span two shelves

Real questions often need data from several tables. Suppose one table lists cities and
another lists the states they belong to:

SELECT cities.name, states.name
FROM cities
JOIN states ON cities.state_id = states.id
ORDER BY cities.name;

+---------------+------------+
| name          | name       |
+---------------+------------+
| San Francisco | California |
| San Jose      | California |
| Page          | Arizona    |
+---------------+------------+

Behind the scenes the engine matches each city's state_id to a state's id. If
states.id is indexed (it is, because it's a primary key), each match is a fast
catalog lookup instead of a full scan. The optimizer decides the join order and the
join algorithm — again, without you having to think about it.

Transactions: all-or-nothing safety

Databases also promise that your data stays consistent, even if the power dies
mid-operation. Picture moving a book between two branches: you must remove it from
branch A and add it to branch B. Doing only half would lose the book.

A transaction groups steps so they all succeed or all fail together:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- take from Alex
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- give to Bob
COMMIT;   -- both happen, or...
-- ROLLBACK;  -- ...neither does

If anything goes wrong before COMMIT, a ROLLBACK undoes everything, as if it never
happened. This guarantee is summarized by the letters ACID:

Letter	Meaning	Plain English
A	Atomicity	All steps happen, or none do.
C	Consistency	The data obeys its rules before and after.
D	Durability	Once committed, it survives crashes.
I	Isolation	Concurrent users don't step on each other.

Putting it all together

Let's trace one realistic query from start to finish:

SELECT tv_genres.name, SUM(tv_show_ratings.rate) AS rating
FROM tv_genres
JOIN tv_show_genres ON tv_genres.id = tv_show_genres.genre_id
JOIN tv_show_ratings ON tv_show_genres.show_id = tv_show_ratings.show_id
GROUP BY tv_genres.name
ORDER BY rating DESC;

name       | rating
-----------+-------
Drama      |   150
Comedy     |    92
Adventure  |    79
Fantasy    |    79

1. Parser: confirms the SQL is valid and the tables/columns exist.
2. Optimizer: decides which table to start from and which indexes (the primary keys on id and show_id) to use for the joins.
3. Executor: joins the three tables, adds up each genre's ratings (SUM), groups by genre, and sorts by the total.
4. Storage engine: physically reads the rows from disk (or from its memory cache if they were read recently) and streams them back.

You wrote one sentence. The engine did all of that automatically.

Conclusion

A SQL database engine is a tireless librarian for your data. You hand it a
description of what you want, and it:

• Parses your request to make sure it makes sense,
• Optimizes it by choosing the cheapest plan among many,
• Executes that plan step by step,
• Stores and retrieves the real bytes through a storage engine, and
• Protects your data with transactions and the ACID guarantees.

Indexes are the catalog that turns "check a million books" into "walk straight to the
shelf," and joins let one question span many tables. The magic is that you never have
to describe how — you only say what, and the engine figures out the rest.

---

Thanks for reading! If this made SQL engines click for you, drop a ❤️ and share it on
LinkedIn — and connect with me there.