MySQL#

Relational Strong consistency Storage engines

What it is#

MySQL is an open source relational database, owned by Oracle since 2010, with MariaDB as its most popular community fork. It's the M in the classic LAMP stack and still runs a huge share of the web's CMSes, e-commerce platforms, and internal tools.

How it works under the hood#

MySQL's defining trait is pluggable storage engines: the SQL layer on top is the same, but the actual storage and locking behavior underneath can differ. InnoDB is the default and only one most people use today, it's transactional, MVCC-based, and row-locking. The older MyISAM engine is table-locking and non-transactional, mostly a historical footnote now.

Setting it up#

Docker

docker run --name mysql-db -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -d mysql:8

Ubuntu

sudo apt install mysql-server
sudo mysql_secure_installation

macOS (Homebrew)

brew install mysql
brew services start mysql

Your first queries#

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO orders (customer_id, total) VALUES (42, 19.99);

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
ORDER BY spent DESC
LIMIT 10;

Notable functionality#

Replication

Classic binlog-based async replication
Group Replication for multi-primary setups
Widely supported by every managed cloud offering

JSON support

Native JSON column type since 5.7
Generated columns can index into JSON fields
Not as deep as Postgres's JSONB, but solid for most needs

Indexing and the clustered key#

InnoDB tables are clustered on the primary key, meaning row data is physically stored in primary key order. A poorly chosen primary key (like a random UUID) can fragment the table and hurt write performance far more than in databases without clustered storage.

Index type	Notes
Primary key	Clustered, meaning the actual row storage order
Secondary index	Stores the primary key value, not a row pointer, one extra lookup
Fulltext	Basic text search, much simpler than Postgres's text search

In production#

-- Find the queries eating the most time right now
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 5;

app → read replica (SELECT)
app → primary (INSERT/UPDATE/DELETE)

Warning

Auto-incrementing integer primary keys and replication don't always play nicely with multi-primary setups, two primaries can generate the same ID for different rows unless you configure auto_increment_offset correctly.

Pros and cons#

Pros

Extremely well understood, huge hosting and tooling ecosystem
Fast for simple read-heavy web workloads
Easy replication setup for read scaling
Free and open source (with a commercial tier from Oracle)

Cons

Historically weaker standards compliance than Postgres
JSON and advanced data types feel bolted on, not native
Clustered primary key design punishes bad key choices
Fewer extensibility options than Postgres's extension system

When to reach for it#

Good fits: WordPress and other PHP-ecosystem applications, teams that already have deep MySQL operational experience, and read-heavy web applications where the tooling and hosting ecosystem matters as much as the database engine itself.

When it's not the right fit#

Applications leaning heavily on advanced SQL (window functions history was rocky, though modern MySQL has caught up) or rich JSON querying
Teams that want a single extensible platform instead of bolting on separate tools for search, geospatial, etc.

read-write-splitting covers the replica patterns MySQL setups lean on heavily.