MySQL

MySQL is the world's most widely deployed open-source relational database. It powers WordPress, Drupal, and a huge share of web applications, and at massive scale it runs parts of Meta, Uber, Shopify, GitHub, and YouTube. Owned by Oracle, it's available as the free Community Edition, a commercial Enterprise Edition, and managed services like Amazon RDS and Aurora, Google Cloud SQL, Azure Database for MySQL, and PlanetScale. MariaDB is a community fork that remains largely compatible.

Almost all modern MySQL workloads use the InnoDB storage engine, which provides ACID transactions, row-level locking, crash recovery, and MVCC. Understanding how InnoDB stores data — especially its clustered primary key — explains most of MySQL's performance behavior.

TL;DR

Quick Example

A schema designed around InnoDB's strengths, a composite index for the main access pattern, and a check of the plan:

The composite index matches the WHERE column first and the ORDER BY column second, so MySQL reads 20 index entries in order instead of sorting all of the customer's orders. Parameterize values from application code to avoid SQL injection:

Core Concepts

InnoDB and the Clustered Index

InnoDB stores each table as a B+tree ordered by primary key; the leaf pages contain the full rows. Consequences:

Indexing

See Database Indexing and Query Optimization.

Transactions, Isolation, and Locking

InnoDB defaults to REPEATABLE READ: a transaction sees a consistent snapshot for plain SELECTs. Locking reads (SELECT ... FOR UPDATE) and writes take row locks and, in ranges, gap and next-key locks that prevent phantom inserts — a frequent source of surprising lock waits and deadlocks. Many applications switch to READ COMMITTED to reduce gap locking. See Database Transactions.

Character Sets and Collations

MySQL's legacy utf8 is actually a 3-byte subset (utf8mb3) that can't store emoji or some CJK characters. Always use utf8mb4. The collation (utf8mb4_0900_ai_ci) controls comparison and sorting — accent- and case-insensitive by default.

Replication and High Availability

Use GTIDs (global transaction identifiers) to simplify failover and replica management. Replicas can lag, so route read-after-write traffic to the primary. See Database Replication.

JSON and Modern Features

MySQL 8 adds a native JSON type with functions and indexes on generated columns, window functions, common table expressions, CHECK constraints, descending indexes, instant ADD COLUMN, and EXPLAIN ANALYZE. MySQL now ships LTS releases (8.4) alongside innovation releases.

Best Practices

Choose Primary Keys Deliberately

Short, monotonically increasing keys keep inserts sequential and secondary indexes small. Expose a separate public identifier if you don't want sequential IDs in URLs.

Design Composite Indexes for Real Queries

Order columns by equality filters first, then range or sort columns. Verify with EXPLAIN and remove indexes that nothing uses (sys.schema_unused_indexes).

Size the Buffer Pool

innodb_buffer_pool_size is the most important setting; on a dedicated server it's commonly 60–75% of RAM so the working set stays in memory.

Enable the Slow Query Log

Log queries above a threshold and analyze them with pt-query-digest or Performance Schema to find what actually costs time.

Change Schemas Online

Use ALGORITHM=INSTANT or INPLACE where supported, and tools like gh-ost or pt-online-schema-change for large tables. See Database Migrations.

Back Up and Test Restores

Use physical backups (Percona XtraBackup, MySQL Enterprise Backup) or managed snapshots plus binary logs for point-in-time recovery. See Database Backups.

Common Mistakes

Using utf8 Instead of utf8mb4

Leads to errors or silently truncated data when users submit emoji or certain characters.

UUIDv4 Strings as Primary Keys

CHAR(36) random UUIDs make every secondary index larger and inserts slower. Use BINARY(16) with time-ordered UUIDs, or an integer key.

Functions on Indexed Columns

WHERE DATE(created_at) = '2026-09-26' prevents index use. Use a range: created_at >= '2026-09-26' AND created_at < '2026-09-27'.

Long Transactions

Open transactions hold locks and prevent purge of old row versions, bloating the undo log and slowing everything.

Relying on Implicit Behavior of Old SQL Modes

Non-strict SQL modes silently truncate or coerce bad data. Keep STRICT_TRANS_TABLES (the default in modern versions) enabled.

Comparison: MySQL vs PostgreSQL

For a detailed comparison, see PostgreSQL vs MySQL.

FAQ

What is MySQL used for?

MySQL stores relational data for web applications, content management systems, e-commerce platforms, and SaaS products. It's a default component of the LAMP stack and widely available as a managed cloud service.

What is InnoDB?

InnoDB is MySQL's default storage engine. It provides ACID transactions, row-level locking, MVCC, foreign keys, and crash recovery, and stores tables clustered by primary key.

Is MySQL free?

The MySQL Community Edition is free and open source under the GPL. Oracle sells a commercial Enterprise Edition with extra tooling and support. MariaDB is a fully open-source fork.

MySQL or PostgreSQL — which should I choose?

Both are excellent. PostgreSQL is often preferred for complex queries, advanced data types, and extensions; MySQL is a strong fit for straightforward, read-heavy web workloads and teams with MySQL experience. Managed services exist for both on every major cloud.

How do I scale MySQL?

Optimize queries and indexes first, then scale reads with replicas and caching (for example Redis). For very large write workloads, shard with tools like Vitess or use a distributed MySQL-compatible service.

Related Topics

References