DEV Community: Ishimwe Charles

How Do SQL Database Engines Work?

Ishimwe Charles — Sat, 01 Aug 2026 19:40:01 +0000

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

Parser: confirms the SQL is valid and the tables/columns exist.
Optimizer: decides which table to start from and which indexes (the primary keys on id and show_id) to use for the joins.
Executor: joins the three tables, adds up each genre's ratings (SUM), groups by genre, and sorts by the total.
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.

Python: Mutable, Immutable... Everything is an Object!

Ishimwe Charles — Mon, 27 Jul 2026 16:40:20 +0000

Introduction

In most programming languages you learn early on to separate values from
variables: a variable is a labelled box, and you drop a value inside it. Python
quietly breaks that mental model. In Python, everything is an object — integers,
strings, lists, functions, even classes themselves — and a variable is never the box.
It is a name that points at an object living somewhere in memory.

That single idea explains a surprising number of "wait, what?" moments: why changing
one list seems to change another, why a is b is sometimes True and sometimes
False for equal values, and why a function can modify your list but not your integer.
Let's unpack it.

`id` and `type`

Every object in Python carries two things we can always ask about: what it is and
where it is.

type(obj) tells you the object's type (its class).
id(obj) returns a unique identifier for the object. In CPython (the reference implementation) this identifier is the object's memory address.

>>> a = 89
>>> type(a)
<class 'int'>
>>> id(a)
140234981234567
>>> type("Best School")
<class 'str'>
>>> type([1, 2, 3])
<class 'list'>

Two names refer to the same object if and only if they share the same id. That
is exactly what the is operator checks, while == only checks whether the values
are equal:

>>> a = [1, 2, 3]
>>> b = [1, 2, 3]
>>> a == b      # same value?
True
>>> a is b      # same object?
False
>>> id(a) == id(b)
False

Mutable objects

A mutable object can be changed in place after it is created — its id stays
the same while its contents change.

The mutable built-in types are:

list
dict
set
bytearray

>>> my_list = [1, 2, 3]
>>> id(my_list)
140234981000000
>>> my_list.append(4)
>>> my_list
[1, 2, 3, 4]
>>> id(my_list)
140234981000000   # SAME id — we mutated the very same object

Immutable objects

An immutable object can never be changed after creation. Any operation that looks
like a change actually builds a brand-new object.

The immutable built-in types are:

Numbers: int, float, complex
str (string)
tuple
frozenset
bytes

>>> n = 10
>>> id(n)
140234980000100
>>> n += 5          # looks like a mutation...
>>> n
15
>>> id(n)
140234980000260   # ...but it's a DIFFERENT object

The name n was simply re-pointed at a new integer object 15. The original 10
was never touched.

Assignment vs. referencing

This is the distinction that trips everyone up.

Referencing (aliasing): b = a does not copy anything. It makes b a second name for the same object a points to.
Assignment to a name: a = something re-binds the name a to a new object. It never changes the object a previously referred to.

>>> a = [1, 2, 3]
>>> b = a          # b is an ALIAS of a — same object
>>> b is a
True
>>> a = a + [4]    # re-binds the NAME a to a new list
>>> a
[1, 2, 3, 4]
>>> b
[1, 2, 3]          # b still points at the original object
>>> a is b
False

How an immutable object is stored in memory

Because immutable objects can never change, CPython is free to reuse them. When
you write the literal 89 in two places, the interpreter can safely hand back the
same object both times — there is no risk that someone mutates it and surprises
everyone else. This is why identity results can look strange for immutables.

Memory schemas (the pictures)

Schema 1 — aliasing a mutable object

a = [1, 2, 3]
b = a
b.append(4)

   Names            Objects in memory
   -----            -----------------
   a ---------\
               >---> [ 1, 2, 3, 4 ]   (id: 0x1000)
   b ---------/

Both names point to ONE list. Mutating through b is visible through a.
print(a)  ->  [1, 2, 3, 4]

Schema 2 — rebinding vs. mutating

l1 = [1, 2, 3]
l2 = l1
l1 = l1 + [4]        # rebinding: builds a NEW list

   Names            Objects in memory
   -----            -----------------
   l2 --------> [ 1, 2, 3 ]        (id: 0x2000)
   l1 --------> [ 1, 2, 3, 4 ]     (id: 0x2500)  <- new object

print(l2)  ->  [1, 2, 3]     (unchanged)


Compare with in-place mutation:
l1 = [1, 2, 3]
l2 = l1
l1 += [4]            # in-place extend: SAME object

   l1 -------\
              >---> [ 1, 2, 3, 4 ]   (id: 0x2000, unchanged id)
   l2 -------/

print(l2)  ->  [1, 2, 3, 4]

That + vs += difference is real and worth internalizing:

>>> a = [1, 2, 3, 4]
>>> before = id(a)
>>> a = a + [5]        # new object
>>> id(a) == before
False
>>> a = [1, 2, 3]
>>> before = id(a)
>>> a += [4]           # in-place, same object
>>> id(a) == before
True

Why it matters: how Python treats mutable and immutable objects differently

Immutable objects are safe to share. Since nobody can change them, CPython reuses and caches them freely. Equality and identity often coincide for small, common immutables.
Mutable objects are dangerous to share accidentally. Two names pointing at the same list means a change through one name is seen through the other. This is a feature when you want it and a bug when you don't — which is why you copy lists (new = old[:] or list(old)) when you need independence.

How arguments are passed to functions

Python passes arguments by assignment (often called "pass by object reference").
The parameter name inside the function becomes a new name bound to the same object
the caller passed in. What happens next depends entirely on whether that object is
mutable and on what you do with it.

Immutable argument — the caller is unaffected:

def increment(n):
    n += 1          # rebinds the LOCAL name to a new int
                    # the caller's object is untouched

a = 1
increment(a)
print(a)            # 1

Mutable argument, mutated in place — the caller sees the change:

def increment(n):
    n.append(4)     # mutates the SAME list object

l = [1, 2, 3]
increment(l)
print(l)            # [1, 2, 3, 4]

Mutable argument, but rebinding inside — the caller is unaffected:

def assign_value(n, v):
    n = v           # rebinds the LOCAL name only

l1 = [1, 2, 3]
l2 = [4, 5, 6]
assign_value(l1, l2)
print(l1)           # [1, 2, 3]   (unchanged — we only moved a local name)

The rule of thumb: mutating an object (calling a method like .append, or index
assignment) affects everyone who shares it; rebinding a name (n = ...) affects
only that name.

Integer pre-allocation and the small-integer cache

Here is where identity gets genuinely surprising:

>>> a = 89
>>> b = 89
>>> a is b
True                # same object!

>>> a = 1000
>>> b = 1000
>>> a is b
False               # different objects

When CPython starts up, it pre-allocates a pool of small integer objects and keeps
them around for the entire lifetime of the program. Any time you use one of these
values, you get the same cached object instead of a freshly created one. That's why
89 is 89 is True but 1000 is 1000 is not.

NSMALLPOSINTS and NSMALLNEGINTS

The size of that cache is defined in CPython's source (Objects/longobject.c) by two
constants:

NSMALLPOSINTS = 257 → the positive integers 0 through 256
NSMALLNEGINTS = 5 → the negative integers -5 through -1

Together they cache the integers in the range -5 to 256, which is
5 + 257 = 262 pre-allocated integer objects.

>>> a = 256
>>> b = 256
>>> a is b
True                # 256 is cached

>>> a = 257
>>> b = 257
>>> a is b
False               # 257 is outside the cache

Why those particular values?

The range -5 to 256 was chosen because these are the most frequently used
integers in real programs: loop counters, small indices, byte values (0–255),
flags, and the like. Caching them avoids constantly allocating and freeing the same
tiny objects, which is a real performance and memory win. Values outside that range
are rare enough that caching them wouldn't pay off.

⚠️ Never rely on this in your code. The small-integer cache is an implementation
detail of CPython. Use == to compare values and reserve is for identity checks
like x is None.

The special case of tuples and frozensets

Tuples and frozensets are immutable containers — you cannot add, remove, or replace
their elements. But there's a subtle catch: immutability of the container does not
make its contents immutable. A tuple can hold a mutable object, and that object can
still change:

>>> t = (1, 2, [3, 4])
>>> t[2].append(5)
>>> t
(1, 2, [3, 4, 5])       # the tuple's list element changed!
>>> t[2] = [9]          # but you can't rebind a slot
Traceback (most recent call last):
  ...
TypeError: 'tuple' object does not support item assignment

So the tuple itself never changed which objects it references — that binding is
frozen — but a mutable object it references can still be mutated internally. This is
also why a tuple containing a list is not hashable (and therefore can't be a dict
key or set member), while a tuple of only immutables is.

Also note the empty-tuple singleton and single-element quirks:

>>> a = ()
>>> b = ()
>>> a is b
True                # CPython uses a single shared empty tuple

>>> a = (1)         # NOT a tuple — just the int 1 in parentheses
>>> type(a)
<class 'int'>
>>> a = (1,)        # the trailing comma makes it a tuple
>>> type(a)
<class 'tuple'>

Conclusion

Once you internalize that variables are names, not boxes, and that every value is
an object with an identity (id), a type (type), and a mutability, Python's behavior
stops being mysterious:

Assignment binds a name; it never copies an object.
Aliasing lets two names share one object — harmless for immutables, powerful (and occasionally dangerous) for mutables.
Functions receive references by assignment, so mutation leaks out but rebinding doesn't.
CPython caches small integers (-5 to 256) and the empty tuple for performance.
Immutable containers freeze their bindings, not necessarily their contents.

Keep == for values, is for identity, and you'll write code that behaves exactly
the way you expect.

Thanks for reading! If this helped clarify Python's object model, drop a ❤️ and a
comment — and connect with me on LinkedIn.

DEV Community: Ishimwe Charles

How Do SQL Database Engines Work?

Introduction

What is SQL, really?

The journey of a single query

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

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

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

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

Indexes: the library catalog

Joins: answering questions that span two shelves

Transactions: all-or-nothing safety

Putting it all together

Conclusion

Python: Mutable, Immutable... Everything is an Object!

Introduction

id and type

Mutable objects

Immutable objects

Assignment vs. referencing

How an immutable object is stored in memory

Memory schemas (the pictures)

Schema 1 — aliasing a mutable object

Schema 2 — rebinding vs. mutating

Why it matters: how Python treats mutable and immutable objects differently

How arguments are passed to functions

Integer pre-allocation and the small-integer cache

NSMALLPOSINTS and NSMALLNEGINTS

Why those particular values?

The special case of tuples and frozensets

Conclusion

`id` and `type`