Contact

The MySQL database: a complete overview, pros, cons and limits

What MySQL is used for, where it is the best choice and where another database wins: pros and cons, versions 8.4 and 9, a comparison with PostgreSQL, MariaDB and SQLite, tools, SQL examples, limits and tips.

Stack and technologies Updated

In short

MySQL is a free relational database owned by Oracle and the most widespread database of the web: WordPress, most CMS and shop platforms run on it, and so do GitHub, Shopify and Booking.com. Its strengths are simplicity of operation, cheap connections, very mature replication and availability on any hosting, including the cheapest. MySQL 8 caught up on modern SQL — window functions, CTEs, JSON, CHECK constraints, instant schema changes. Its weak spots compared with PostgreSQL are fewer data types and extensions, no RETURNING and schema changes that commit the transaction by themselves. Version 8.0 reached the end of support in April 2026 — the long-term version now is 8.4.

MySQL at a glance

The main facts in one table — where the database came from, how it stores data and how it evolves.

Type
Relational database management system with open source code
History
MySQL AB, Sweden, 1995; Sun from 2008; Oracle since 2010
License
Community edition under GPL v2; Oracle also sells Enterprise and a commercial licence
Storage engine
InnoDB by default since 2010: transactions, row locks, a clustered primary key
Transactions
ACID and MVCC through an undo log — no vacuum needed
SQL
Since 8.0: window functions, CTEs, JSON functions, CHECK, EXPLAIN ANALYZE
Indexes
B-tree, full-text, spatial; by expression, descending, invisible
Replication
Asynchronous and semi-synchronous; Group Replication and InnoDB Cluster
Versions
8.4 is the long-term version; 9.x are innovation releases; 8.0 support ended in April 2026
Forks
MariaDB (by MySQL’s author) and Percona Server
Built on it
WordPress, GitHub, Shopify, Booking.com

What MySQL is used for: 8 areas

MySQL grew up with the web and is strongest there. Under each area — the tools it usually comes with.

  1. 01

    Sites on CMS

    The most popular CMS are built for MySQL and its forks.

    WordPressJoomlaDrupal

  2. 02

    Online shops

    Orders, stock and customers on ready platforms and own code.

    MagentoPrestaShopWooCommerce

  3. 03

    Web applications

    Classic backends in PHP, Ruby, Python and Go.

    LaravelRailsDjango

  4. 04

    Many simple reads

    Lookups by key at high volume — with read replicas.

    ProxySQLreplicas

  5. 05

    Huge horizontal scale

    Sharding through Vitess — the system that grew out of YouTube.

    VitessPlanetScale

  6. 06

    High availability

    Automatic switching to a replica when the primary fails.

    InnoDB ClusterMySQL Router

  7. 07

    Shared hosting

    A database on any hosting plan without separate administration.

    phpMyAdminAdminer

  8. 08

    Managed cloud databases

    Every large cloud offers MySQL with backups and replicas out of the box.

    Amazon RDSAuroraCloud SQL

Pros and cons of MySQL

MySQL bets on simplicity and speed of everyday operations. Most of its strengths and limits come from that bet.

Pros · 8

  • Simple to run

    Sensible defaults, no vacuum, a predictable life on modest hardware.

  • Cheap connections

    A thread per connection: hundreds of web processes without a pooler.

  • Mature replication

    Read replicas are set up quickly; Group Replication and InnoDB Cluster give failover.

  • Everywhere

    On any hosting and in every cloud — no question “is it supported?”.

  • The ecosystem of the web

    Most CMS and shop platforms, admin tools and hosting panels are built around it.

  • Modern SQL in 8.x

    Window functions, CTEs, JSON and CHECK constraints closed the old gaps.

  • Online schema changes

    ALGORITHM=INSTANT adds a column without rewriting the table; gh-ost does the rest.

  • Scale proven by giants

    GitHub, Shopify and Booking.com run on it; Vitess shards it horizontally.

Cons · 8

  • Owned by Oracle

    The roadmap is set by one company; some features live only in the paid editions and the cloud.

  • No transactional DDL

    Each statement is atomic, but ALTER TABLE commits the transaction — a failed migration is not rolled back.

  • No RETURNING

    The inserted row has to be read with a second query.

  • Fewer types and extensions

    No arrays, ranges or own types; nothing like PostGIS or pgvector in the open edition.

  • No partial indexes

    An index always covers the whole table, even if queries only need a small part of it.

  • Lax legacy settings

    Old installations may silently cut strings and accept wrong dates.

  • Forks drift apart

    MariaDB is no longer a drop-in replacement: JSON and replication behave differently.

  • Row storage for analytics

    Scanning billions of rows for reports is a job for columnar databases.

MySQL compared with PostgreSQL, MariaDB and SQLite

A qualitative comparison with the databases MySQL is most often weighed against.

CriterionMySQLPostgreSQLMariaDBSQLite
Owner Oracle a community MariaDB Foundation and company public domain
Server yes yes yes no, a file
Simplicity of operation high medium high the highest
SQL and types modern basics the richest close to MySQL, own extras basic
Schema change in a transaction no yes no yes
Replication very mature mature mature, Galera none built in
Hosting everywhere VPS and cloud often instead of MySQL anywhere, it is a file
Best for CMS, shops, web apps services, SaaS, complex data CMS on hosting small sites and apps

When to choose MySQL — and when not to

Twelve typical tasks with a verdict. Where MySQL is not the best choice, the alternative is named.

  • A site on WordPress or another CMS

    Best fit

    The CMS is built for it.

  • A shop on a ready platform

    Best fit

    Most platforms expect MySQL.

  • A web app with simple data

    Best fit

    Reliable, fast and simple to run.

  • Shared hosting

    Best fit

    It is there on every plan.

  • Many reads with replicas

    Best fit

    Replication is MySQL’s strong side.

  • A team experienced with MySQL

    Best fit

    Experience matters more than small differences.

  • JSON-heavy data

    Works

    Works through generated columns; PostgreSQL with jsonb is more convenient.

  • Site search

    Works

    Built-in full-text search is enough for simple cases; beyond that — Meilisearch.

  • Geodata and maps

    Pick another

    PostgreSQL with PostGIS.

  • AI search over vectors

    Pick another

    PostgreSQL with pgvector: indexes in the open version.

  • Complex domain with strict rules

    Pick another

    PostgreSQL: richer types, transactional migrations.

  • Analytics over billions of events

    Pick another

    ClickHouse: columnar storage.

The MySQL ecosystem: tools for common tasks

The middle column is what ships with MySQL itself.

TaskBuilt inTools
Backups mysqldump, MySQL Shell Percona XtraBackup
Online schema changes ALGORITHM=INSTANT gh-ost, pt-online-schema-change
Connection routing MySQL Router ProxySQL
High availability Group Replication, InnoDB Cluster Orchestrator
Sharding — Vitess, PlanetScale
Slow queries slow query log, EXPLAIN ANALYZE pt-query-digest
Monitoring Performance Schema, sys PMM, mysqld_exporter
Administration mysql, MySQL Shell phpMyAdmin, Adminer, DBeaver
Migrations — Flyway, Liquibase, Atlas

The limits of MySQL: where it hits the ceiling

  1. A failed migration

    Schema changes are not rolled back with the transaction — a half-applied migration is fixed by hand.

  2. Large ALTER on a busy table

    Not every change is instant; for the rest use gh-ost so the site does not stop.

  3. Random primary keys

    InnoDB stores rows in the order of the key; random UUIDs scatter inserts and bloat the table.

  4. Analytics in the main database

    Heavy reports slow down the site; they belong on a replica or in a columnar database.

  5. Data needs beyond tables

    Geodata, vectors and complex types push the project towards PostgreSQL.

  6. Staying on 8.0

    After April 2026 it gets no security fixes — the move to 8.4 should be planned.

8 tips for working with MySQL

  1. 01

    utf8mb4 everywhere

    The old utf8 cannot store emoji and some characters; utf8mb4 is the real UTF-8.

  2. 02

    Strict mode on

    Wrong data should be an error, not silently cut values.

  3. 03

    Short, growing primary keys

    BIGINT AUTO_INCREMENT or time-ordered UUIDs — not random ones.

  4. 04

    The slow query log

    It shows what to optimise; pt-query-digest groups it into a report.

  5. 05

    EXPLAIN ANALYZE before an index

    See the real plan, and hide an index before deleting it.

  6. 06

    Hot backups

    XtraBackup copies a working database without stopping it; test restores regularly.

  7. 07

    Reports on a replica

    Heavy reads do not compete with the site for the primary server.

  8. 08

    Plan the move to 8.4

    Check removed settings and plugins on a copy before upgrading production.

What MySQL looks like: 3 SQL examples

A report with a window function, changing the schema without stopping, and reading a query plan. Checked on MySQL 8.4.

A report with a window function

The top two products in each category in one query — what used to take loops in code.

top_products.sql 
-- the top two products by revenue in each category: a CTE and a window function
CREATE TABLE sales (
    product  VARCHAR(40)   NOT NULL,
    category VARCHAR(20)   NOT NULL,
    revenue  DECIMAL(10,2) NOT NULL CHECK (revenue >= 0)
);
INSERT INTO sales VALUES
    ('Oak table', 'tables', 120000), ('Pine table', 'tables', 45000), ('Glass table', 'tables', 80000),
    ('Oak chair', 'chairs', 30000),  ('Bar stool', 'chairs', 52000);

WITH ranked AS (
    SELECT product, category, revenue,
           RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS place
    FROM sales
)
SELECT category, product, revenue
FROM ranked
WHERE place <= 2
ORDER BY category, place;
-- chairs | Bar stool | 52000.00
-- chairs | Oak chair | 30000.00
-- tables | Oak table | 120000.00
-- tables | Glass table | 80000.00

Changing the schema without stopping

An instant new column and an invisible index — a safe way to check whether an index is still needed.

schema.sql 
CREATE TABLE orders (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    email      VARCHAR(190) NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX orders_email_idx (email)
);

-- a new column without rewriting the table: instant even on millions of rows
ALTER TABLE orders ADD COLUMN source VARCHAR(30) NULL, ALGORITHM=INSTANT;

-- before deleting an index, hide it: if queries slow down, show it again
ALTER TABLE orders ALTER INDEX orders_email_idx INVISIBLE;
ALTER TABLE orders ALTER INDEX orders_email_idx VISIBLE;

SELECT index_name, is_visible FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'orders' AND index_name = 'orders_email_idx';
-- orders_email_idx | YES

Reading the query plan

EXPLAIN ANALYZE shows that the query uses the index rather than reading the whole table.

explain.sql 
-- EXPLAIN ANALYZE runs the query and shows where the time went
INSERT INTO orders (email) VALUES ('[email protected]'), ('[email protected]'), ('[email protected]');

EXPLAIN ANALYZE
SELECT id, created_at FROM orders WHERE email = '[email protected]';
-- -> Index lookup on orders using orders_email_idx (email='[email protected]')

Questions about MySQL

Is MySQL free?

The Community edition is free under GPL v2. Oracle also sells Enterprise with extra tools and support.

Which version should I use?

8.4, the long-term version. Support for 8.0 ended in April 2026.

MySQL or MariaDB?

For a CMS on hosting either works. They have drifted apart, so a project written for one may need changes for the other.

MySQL or PostgreSQL?

MySQL for CMS, shared hosting and simple data; PostgreSQL for complex data, services and AI search.

Can MySQL handle high load?

Yes: GitHub and Shopify run on it, replicas scale reads and Vitess scales writes horizontally.

Does MySQL support JSON?

Yes, a JSON type with functions; fields are indexed through generated columns.

Why is my emoji saved as question marks?

The table uses the old utf8; switch to utf8mb4.

How do I back up MySQL without stopping the site?

Percona XtraBackup or MySQL Shell dumps copy a working database; test restoring the copy.

Online form

MySQL
databases

I work with MySQL in my projects: I design the schema, speed up slow queries, set up indexes, replication and backups, and move data between MySQL and PostgreSQL. Tell me about the task — I answer within one working day.

Or write to [email protected]