MySQL

What is MySQL?

MySQL is an open-source relational database that became the default storage layer of the early web. It was released in 1995 with a focus on speed and simplicity for read-heavy sites, and it grew alongside the LAMP stack — Linux, Apache, MySQL and PHP — into one of the most widely deployed databases in existence.

Oracle now develops it, but a large community and a rich set of managed services keep it everywhere. WordPress, Magento, Shopify-style commerce platforms and countless custom applications run on MySQL. If you have used a website with a login form, there is a good chance a MySQL table was involved.

The modern MySQL is not the simple engine of the 1990s. Since version 8 it has a transactional data dictionary, common table expressions, window functions and capable JSON support. Learning it today means learning a serious relational database that also happens to be extraordinarily well supported.

Where MySQL runs

MySQL’s biggest practical advantage is its ubiquity. Almost every shared host, cloud provider and platform-as-a-service offers it. Frameworks ship drivers, ORMs support it out of the box, and DBAs have decades of experience with it. That reduces the cost of everything around the database: hiring, tooling, monitoring and migration.

It is the usual choice for content management systems, e-commerce, SaaS backends and any application where the ecosystem matters as much as the engine. It scales from a single small instance to sharded clusters, and the path from one to the other is well documented.

InnoDB is the engine that matters

MySQL has a pluggable storage engine architecture, but in practice you will use InnoDB. It provides:

• ACID transactions with COMMIT and ROLLBACK.
• Row-level locking, so writers do not block readers.
• Foreign keys and constraint enforcement.
• Crash recovery through a redo log.
• MVCC for consistent reads.

The older MyISAM engine lacked transactions and row-level locking. It has no place in a new schema. Always declare ENGINE=InnoDB explicitly so the choice is visible and does not depend on server defaults.

CREATE TABLE products (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku         VARCHAR(64) NOT NULL,
  name        VARCHAR(200) NOT NULL,
  price_cents INT UNSIGNED NOT NULL,
  stock       INT NOT NULL DEFAULT 0,
  attributes  JSON NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_products_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Data types and AUTO_INCREMENT

MySQL’s type system is pragmatic. The common choices:

• INT and BIGINT — whole numbers; add UNSIGNED for IDs and counts that cannot be negative.
• VARCHAR(n) — variable-length text with a maximum. Unlike Postgres, MySQL genuinely benefits from a sensible length.
• TEXT — large text stored off the row when needed.
• DECIMAL(p, s) — exact decimals, the correct type for money if you do not use integer minor units.
• TIMESTAMP and DATETIME — timestamps; TIMESTAMP converts to UTC and has a range limit, while DATETIME stores what you give it.
• JSON — a validated JSON document stored efficiently.
• ENUM — a fixed set of strings; convenient, but changing the list requires a schema change.

AUTO_INCREMENT generates the next integer for a column, almost always the primary key. It is fast and gap-tolerant: rolled-back inserts consume a value, and concurrent inserts may not produce contiguous numbers. Never rely on the ID being gapless or on its order meaning anything.

INSERT INTO products (sku, name, price_cents, stock, attributes)
VALUES ('SKU-1', 'Widget', 999, 10, JSON_OBJECT('colour', 'blue'));

SELECT LAST_INSERT_ID();

CRUD without surprises

The four basic operations map to four statements. Read them as a set, because the shape repeats.

INSERT INTO products (sku, name, price_cents, stock, attributes)
VALUES ('SKU-2', 'Gadget', 1499, 5, JSON_OBJECT('colour', 'red'));

SELECT id, sku, name, price_cents
FROM products
WHERE stock > 0
ORDER BY price_cents ASC
LIMIT 20 OFFSET 40;

UPDATE products
SET price_cents = 1299, stock = stock - 1
WHERE id = 42;

DELETE FROM products
WHERE stock = 0 AND created_at < NOW() - INTERVAL 90 DAY;

Two habits prevent the classic accidents. First, always include a WHERE clause on UPDATE and DELETE; without one, every row changes. Second, run the equivalent SELECT first to confirm which rows you are about to affect. MySQL has a sql_safe_updates mode that refuses statements without a key in the WHERE, and enabling it in development is a cheap safeguard.

LIMIT with OFFSET paginates, but large offsets scan and discard rows. For deep pagination prefer keyset pagination: WHERE id > :last_id ORDER BY id LIMIT 20.

Joins and aggregation

Joins combine tables on a matching condition, exactly as in standard SQL.

SELECT c.name             AS category,
       COUNT(*)           AS product_count,
       SUM(p.price_cents) AS inventory_value
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.stock > 0
GROUP BY c.id, c.name
ORDER BY inventory_value DESC
LIMIT 20;

JOIN keeps matching pairs, LEFT JOIN keeps all left rows and fills missing right columns with NULL. Aggregates such as COUNT, SUM, AVG, MIN and MAX collapse groups; every non-aggregated selected column must be grouped.

MySQL historically allowed selecting non-grouped columns and returned an arbitrary value, which hid bugs. With ONLY_FULL_GROUP_BY enabled — the default in MySQL 8 — the server rejects ambiguous queries, which is what you want. Do not disable it to make an old query run; fix the query.

WHERE filters rows before grouping and HAVING filters after, so conditions on aggregates belong in HAVING.

Indexes and EXPLAIN

An index is a sorted structure that avoids a full table scan. MySQL creates one for the primary key and for every UNIQUE constraint automatically. Add others for the columns you filter, join and sort on.

CREATE INDEX idx_products_price ON products (price_cents);

A composite index covers several columns and follows the leftmost-prefix rule: an index on (category_id, price_cents) helps queries that filter on category_id, or on category_id and price_cents, but not on price_cents alone. Order columns from the most selective and most frequently filtered outward.

Ask the optimizer what it will do:

EXPLAIN
SELECT id, name, price_cents
FROM products
WHERE price_cents < 2500
ORDER BY price_cents
LIMIT 25;

Read the type column first: const, eq_ref and ref are good; range is fine; index and ALL mean a scan. The key column shows which index was chosen, and rows estimates how many it will examine. A large rows for a small result usually means a missing or unusable index. Use EXPLAIN ANALYZE in MySQL 8 to run the query and see real timings.

Covering indexes deserve a mention. If an index contains every column a query needs, MySQL can answer from the index alone and never touch the row. Adding a column to an index purely to make it covering is often a large win.

utf8mb4 and the charset trap

Character sets are where MySQL surprises people. For most of its history the default utf8 was a maximum of three bytes per character, which covers most text but not four-byte code points such as emoji and many rare scripts. Trying to store an emoji in a utf8 column raises an error or truncates the value, depending on the server’s mode.

The fix is utf8mb4, which is real UTF-8 and stores everything. Set it at every level — server, database, table and connection:

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

The collation decides how strings compare and sort. utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive, which is usually what users expect for search. Collations also affect index behaviour, so keep them consistent across joined columns; a mismatch forces conversions that can make an index unusable.

Transactions and isolation levels

InnoDB gives you transactions. Group related writes so they succeed or fail together.

START TRANSACTION;

UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 42 AND quantity >= 1;

INSERT INTO orders (product_id, quantity)
VALUES (42, 1);

COMMIT;

Check the affected rows and ROLLBACK if the guarded update changed nothing. The default isolation level is Repeatable Read, which gives a consistent snapshot for the transaction and, in InnoDB, takes gap locks that prevent phantom rows. The other levels are Read Uncommitted, Read Committed and Serializable.

A useful difference from Postgres: because of gap locking under Repeatable Read, concurrent inserts into a range can block or deadlock more readily. Keep transactions short, update rows in a consistent order, and be prepared to retry a deadlock, which InnoDB reports as an error rather than corrupting state.

Replication and read scaling

Most MySQL deployments scale reads before writes. The primary records every change in its binary log, and one or more replicas connect and replay that log. Replication is asynchronous by default, so a replica can lag behind the primary by milliseconds or more.

The standard pattern is to send writes to the primary and distribute reads across replicas, accepting that a read may briefly return slightly stale data. For read-after-write consistency, route a user’s reads to the primary for a short window after their write, or read the replica only for data that tolerates lag.

Replication also provides high availability. If the primary fails, a replica can be promoted. Tools such as orchestrators and managed services automate that failover, but you still need to understand the trade-off between synchronous and asynchronous modes: synchronous waits for replicas and risks availability, asynchronous risks losing the last few transactions on failover.

Upserts with ON DUPLICATE KEY UPDATE

MySQL’s idiomatic upsert is INSERT ... ON DUPLICATE KEY UPDATE. When the insert would violate a primary or unique key, the update clause runs instead.

INSERT INTO products (sku, name, price_cents, stock, attributes)
VALUES ('SKU-1', 'Widget', 1099, 5, JSON_OBJECT('colour', 'blue'))
ON DUPLICATE KEY UPDATE
  price_cents = VALUES(price_cents),
  stock       = stock + VALUES(stock);

This is atomic, which matters for counters and inventory. The alternative — select, then decide whether to insert or update — has a race window where two sessions both see no row and both insert. Use the upsert whenever the operation is genuinely insert-or-update.

JSON columns

MySQL’s JSON type stores a validated document and supports functions to read and write parts of it.

SELECT id, name,
       attributes->>'$.colour' AS colour
FROM products
WHERE attributes->>'$.colour' = 'blue';

You can index JSON with generated columns. MySQL cannot index a JSON expression directly, so extract it into a stored generated column and index that:

ALTER TABLE products
  ADD COLUMN colour VARCHAR(32)
    GENERATED ALWAYS AS (attributes->>'$.colour') STORED,
  ADD INDEX idx_products_colour (colour);

Use JSON for variable attributes and third-party payloads. Keep fields you filter constantly as real columns; the generated-column trick works, but a native column with a real type is simpler and faster.

Stored procedures: use sparingly

MySQL supports stored procedures, functions, triggers and scheduled events. They can reduce round trips and centralise logic, but they also move business logic into a language that is harder to test, version and debug than your application code.

Use them for genuinely database-shaped work: bulk maintenance, data migrations and jobs that must run close to the data. Avoid putting core domain rules in procedures that only one team understands. Triggers are especially easy to forget; an UPDATE that silently fires three triggers is difficult to reason about, and hidden side effects surprise everyone eventually.

Users, grants and backups

Create a dedicated application user with only the privileges it needs, rather than connecting as root.

CREATE USER 'app'@'%' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';
FLUSH PRIVILEGES;

Keep DDL and migrations on a separate, more privileged account. Restrict hosts where possible, require TLS, and rotate credentials.

For backups, mysqldump produces a logical dump that is easy to move and restore:

mysqldump --single-transaction --routines --triggers shop > shop.sql
mysql shop_restore < shop.sql

The --single-transaction flag takes a consistent snapshot of InnoDB tables without locking them for the whole dump. For large databases where dump time is prohibitive, use a physical tool such as Percona XtraBackup. Either way, record the binary log position so you can recover to a point in time, and test restores.

MySQL vs MariaDB

MariaDB began as a community fork of MySQL after Oracle’s acquisition and has diverged since. It keeps most MySQL syntax and adds its own storage engines and features. MySQL, meanwhile, has moved quickly since version 8 with a new data dictionary, window functions and improved JSON.

For most applications the differences are small. Choose MySQL if your managed provider or support contract is built around it, and MariaDB if you prefer the community-governed project or need one of its engines. The relational concepts, SQL and operational patterns transfer between them, so the choice is rarely a one-way door.

Full-text search inside InnoDB

InnoDB ships with a full-text index, so a search feature does not require a separate service. Create a FULLTEXT index over the text columns and query it with MATCH ... AGAINST.

CREATE FULLTEXT INDEX ft_products_search
  ON products (name, attributes);

SELECT id, name,
       MATCH(name, attributes) AGAINST ('running shoe' IN NATURAL LANGUAGE MODE) AS score
FROM products
WHERE MATCH(name, attributes) AGAINST ('running shoe' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

NATURAL LANGUAGE MODE ranks by relevance and ignores words that appear in most rows. BOOLEAN MODE gives you operators like +must, -exclude and "exact phrase" for more control. The index has a minimum token length controlled by innodb_ft_min_token_size, so very short words may be skipped.

Full-text search is a good fit for product catalogues and article search. Move to a dedicated engine when you need typo tolerance, faceting or cross-language analysis that MySQL does not provide.

Views and generated columns

A view is a named query that behaves like a table, useful for packaging a common shape and for limiting which columns a reporting user can see.

CREATE VIEW in_stock AS
SELECT id, sku, name, price_cents, stock
FROM products
WHERE stock > 0;

SELECT * FROM in_stock WHERE price_cents < 2500;

MySQL 8 supports window functions, so views can also pre-shape analytical results. Remember that a view runs its underlying query each time unless it is materialized by hand into a table; MySQL has no native materialized views, so teams either use a summary table refreshed on a schedule or an event.

Generated columns, shown in the JSON section, are the other half of this idea: a stored generated column is computed on write and can be indexed, while a virtual one is computed on read. Use a stored column when you need to index the expression, and a virtual one when you only need the value occasionally.

Changing schemas on a live database

ALTER TABLE on a large InnoDB table can rebuild the table and hold locks for a long time. MySQL 8 supports online DDL for many operations through ALGORITHM=INPLACE, which rebuilds without blocking concurrent reads and writes in many cases.

ALTER TABLE products
  ADD COLUMN updated_at TIMESTAMP NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE products
  ADD INDEX idx_products_updated (updated_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Not every change is online. Changing a column type, adding a FULLTEXT index or rebuilding a primary key often still copies the table. For those, use a tool such as pt-online-schema-change or gh-ost, which create a shadow table, copy rows in batches and swap it in with minimal blocking. Whatever the method, run schema changes through versioned migrations and test them on a copy of production data first.

Finding slow queries

MySQL records queries that exceed long_query_time in the slow query log, and EXPLAIN shows how a specific query runs. Together they are the fastest path from “the app is slow” to a concrete fix.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;

SHOW VARIABLES LIKE 'slow_query_log_file';

The performance_schema and the sys schema summarise the same data. Start with the statements that consume the most total time, not the single slowest one, and check EXPLAIN for full scans on large tables. SHOW PROFILE and EXPLAIN ANALYZE in MySQL 8 add per-stage timings when you need to go deeper.

Loading data quickly

A row-by-row INSERT loop is the slowest way to load data. Batch many rows into one statement, which reduces round trips and lets InnoDB write pages efficiently.

INSERT INTO products (sku, name, price_cents, stock, attributes)
VALUES
  ('SKU-10', 'Widget', 999, 5, JSON_OBJECT('colour', 'blue')),
  ('SKU-11', 'Gadget', 1499, 3, JSON_OBJECT('colour', 'red')),
  ('SKU-12', 'Gizmo', 2499, 7, JSON_OBJECT('colour', 'green'));

For bulk imports, LOAD DATA INFILE streams a file directly into a table and is dramatically faster than any INSERT form.

LOAD DATA LOCAL INFILE '/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(sku, name, price_cents, stock, @attributes)
SET attributes = CAST(@attributes AS JSON);

Wrap large loads in a transaction so a failure does not leave a half-imported table, and consider disabling secondary indexes or unique checks during a one-off bulk load, then rebuilding them. For everyday application writes, a batched INSERT is the right default.

Best practices

• Use InnoDB for every table and declare it explicitly.
• Create databases, tables and connections with utf8mb4 and a consistent collation.
• Store money as integer minor units or DECIMAL, never as FLOAT or DOUBLE.
• Add indexes for the queries you actually run, and confirm them with EXPLAIN.
• Keep the default ONLY_FULL_GROUP_BY mode and write correct GROUP BY queries.
• Use INSERT ... ON DUPLICATE KEY UPDATE for atomic upserts instead of check-then-act.
• Keep transactions short, update rows in a stable order, and retry on deadlock.
• Give the application a least-privilege user and keep DDL on a migration account.
• Take consistent backups with --single-transaction, record the binlog position, and test restores.
• Prefer keyset pagination over large LIMIT ... OFFSET scans.

Common mistakes

• Leaving tables on MyISAM and losing transactions and row-level locking.
• Using the old three-byte utf8 and then wondering why emoji fail to save.
• Storing money in FLOAT and accumulating rounding errors.
• Running UPDATE or DELETE without a WHERE and rewriting the whole table.
• Indexing every column instead of the queries that matter, which slows writes.
• Relying on AUTO_INCREMENT to be gapless or meaningful.
• Doing read-modify-write in application code instead of an atomic upsert.
• Reading from a replica immediately after a write and seeing stale data.
• Disabling ONLY_FULL_GROUP_BY to hide an incorrect aggregation.
• Letting the application connect as root.

Where to go next

MySQL and its ideas travel well. If you want to compare the other major relational engine, read the PostgreSQL guide, which covers the same concepts with different defaults. For the language underneath both, the SQL guide builds up joins, CTEs and window functions from first principles. And because most MySQL deployments lean on a cache, Redis and the document model in MongoDB are natural companions.