# MySQL: A Practical Guide to the Web's Workhorse Database

You have probably used MySQL today without knowing it. A blog you read, a shop you bought from, a forum you searched: there is a fair chance a MySQL server answered somewhere behind the page.

It earned that place by being easy to install, easy to host and fast at the everyday work of a website. It also has a handful of defaults and habits that catch people out, sometimes years after launch.

This guide covers both sides: what MySQL does well, and the settings and practices that keep it out of trouble.

## What MySQL is

MySQL is an open-source relational database. Your data lives in tables, tables relate to each other through keys, and you query them with SQL.

Oracle owns and develops it. The Community Edition is free under the GPL licence, and there are paid editions with extra tooling. Every large cloud offers it as a managed service, and many cheaper web hosts include it by default.

Two facts explain a lot about how MySQL behaves:

- **Storage engines.** MySQL separates the part that parses SQL from the part that stores rows. The storage engine you want is **InnoDB**, which has been the default for many years. It gives you transactions, row-level locking and crash recovery. The older MyISAM engine has none of those, so treat it as history.
- **Replication is built in.** One server takes the writes and streams its changes to copies called replicas. This is how most MySQL sites scale reads and survive a server failure.

## Why teams choose it

### It is everywhere, and so is the help

Almost every language, framework, hosting company and monitoring tool supports MySQL. When something goes wrong, someone has usually hit the same error before and written down the fix. For a small team, that is worth more than any single feature.

### It is quick at simple work

Most web traffic is short queries: fetch one user, list twenty orders, insert a row. InnoDB stores each table physically sorted by its primary key (a "clustered index"), so looking a row up by primary key is very direct.

### Read scaling is a solved problem

Adding read replicas is routine, and managed services do it with a click. Send reports and heavy read traffic to a replica, and keep the primary server for writes.

### It does more than its old reputation suggests

Modern MySQL (the 8.x series) has window functions, common table expressions (`WITH` queries), a native `JSON` type and `CHECK` constraints that are actually enforced. If your picture of MySQL comes from a decade ago, it is out of date.

## Where MySQL fits, and where it does not

**A strong choice for:**

- Websites, content management systems and online shops
- Application backends with many short reads and writes
- Read-heavy workloads spread across replicas
- Teams that want the widest choice of hosting and tooling

**Look elsewhere when:**

- **You need rich SQL features or special data types.** PostgreSQL goes further with extensions, custom types, location data and advanced indexing.
- **You run analytics over very large tables.** A column store such as ClickHouse or BigQuery is built for scanning billions of rows.
- **You need schema changes inside a transaction.** In MySQL, statements such as `CREATE TABLE` and `ALTER TABLE` commit immediately. You cannot roll back a half-finished migration the way you can in PostgreSQL.
- **You need full-text search with ranking and typo tolerance.** MySQL has basic full-text indexes, but a search engine such as Elasticsearch does this job better.

## Using it the right way

### Start every table the same way

```sql
CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  total_cents INT UNSIGNED NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  details     JSON NULL,
  created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT orders_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

A few choices in there are worth explaining:

- **`utf8mb4`, never `utf8`.** In MySQL, the name `utf8` means an old three-byte encoding that cannot store emoji or some Asian characters. `utf8mb4` is real UTF-8.
- **A short, increasing primary key.** Because InnoDB sorts the table by primary key and copies that key into every other index, a `BIGINT` works better than a long string or a random UUID.
- **Money as whole cents**, or use `DECIMAL(12,2)`. Never `FLOAT` or `DOUBLE`, which round.
- **`DATETIME` over `TIMESTAMP`** for dates far in the future: `TIMESTAMP` stops in 2038. Store times in UTC and convert in the application.

### Keep strict mode on

MySQL once had a habit of quietly "fixing" bad data: cutting a long string short, or storing zero for an invalid number. Strict mode turns those into errors. It is on by default in current versions, but old configuration files sometimes switch it off.

```sql
SELECT @@sql_mode;
```

You want to see `STRICT_TRANS_TABLES` in the answer. If you do not, fix the server configuration before you trust the data.

### Index for your queries, left to right

An index on several columns can be used from its leftmost column onward. Think of a phone book sorted by surname, then first name: useful for finding "Sharma" or "Sharma, Priya", useless for finding everyone called Priya.

```sql
-- Serves: WHERE customer_id = ?  and
--         WHERE customer_id = ? ORDER BY created_at DESC
CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at);
```

MySQL creates an index for a foreign key column if one does not exist, so `customer_id` was already covered. The combined index above replaces it and also handles the sort.

### Ask how a query runs

```sql
EXPLAIN
SELECT id, total_cents FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```

Look at three columns. `type: ALL` means a full table scan. `key` shows which index was chosen, or `NULL` for none. `rows` is the estimate of how many rows MySQL will read. On MySQL 8, `EXPLAIN ANALYZE` runs the query and shows real timings.

To find slow queries in the first place, turn on the slow query log:

```sql
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- seconds
```

### Index inside JSON with a generated column

You cannot index a `JSON` column directly, but you can pull one value out into a generated column and index that:

```sql
ALTER TABLE orders
  ADD COLUMN coupon VARCHAR(40)
    GENERATED ALWAYS AS (details->>'$.coupon') STORED,
  ADD INDEX orders_coupon_idx (coupon);

SELECT id FROM orders WHERE coupon = 'SUMMER10';
```

### Page by key, not by offset

`LIMIT 20 OFFSET 100000` makes MySQL read and throw away 100,000 rows. Remember the last row you showed instead:

```sql
SELECT id, total_cents, created_at FROM orders
WHERE customer_id = 42 AND id < 918273
ORDER BY id DESC
LIMIT 20;
```

### One account per application

```sql
CREATE USER 'shop_app'@'%' IDENTIFIED BY 'use-a-long-random-one';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'%';
```

The part after `@` is the host the account may connect from. `%` means anywhere; narrow it to your application's network when you can. Keep `root` for administration only, and give reporting tools an account with `SELECT` alone.

### Back up without stopping the site

```bash
mysqldump --single-transaction --routines --triggers shop > shop.sql
mysql shop_restored < shop.sql
```

`--single-transaction` takes a consistent snapshot of InnoDB tables without locking them. For large databases a dump becomes slow to restore, so use physical backups or your provider's snapshots, and keep the binary log so you can recover to a point in time. Then restore one, on a schedule, to prove it works.

## Common mistakes

- **Using `utf8`.** The first customer with an emoji in their name gets an error or a mangled record. Use `utf8mb4` everywhere, including the connection settings.
- **Tables still on MyISAM.** No transactions, and a crash can corrupt them. Check with `SHOW TABLE STATUS;` and convert with `ALTER TABLE t ENGINE=InnoDB;`.
- **Random UUIDs as the primary key.** Every insert lands in a random place in the table, which slows writes and bloats indexes. Use an auto-increment key, or a time-ordered identifier.
- **Reading your own write from a replica.** Replicas run slightly behind. A user who saves a form and is sent to a replica may not see the change. Read from the primary straight after a write.
- **Schema changes at peak time.** Many `ALTER TABLE` operations are online in InnoDB, but not all, and even the online ones need a brief lock that waits behind long transactions. Test on a copy first.
- **One huge `DELETE`.** It holds locks and makes replicas fall behind. Delete in batches with `LIMIT`.
- **Trusting `GROUP BY` shortcuts.** Selecting columns that are neither grouped nor aggregated returns arbitrary values on servers with a loose `sql_mode`. Keep `ONLY_FULL_GROUP_BY` on.

## Quick reference

| Task | Command |
|---|---|
| Connect | `mysql -h host -u user -p dbname` |
| List databases | `SHOW DATABASES;` |
| List tables | `SHOW TABLES;` |
| Describe a table | `SHOW CREATE TABLE orders\G` |
| See running queries | `SHOW FULL PROCESSLIST;` |
| Stop a query | `KILL QUERY 1234;` |
| Indexes on a table | `SHOW INDEX FROM orders;` |
| Server version | `SELECT VERSION();` |
| Database sizes | `SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb FROM information_schema.tables GROUP BY table_schema;` |

MySQL does not ask much of you: InnoDB, `utf8mb4`, strict mode, sensible indexes and a backup you have tested. Get those five right and it will run quietly for years.

## Exploring MySQL with CreamDB

A terminal is enough for everything above. When you would rather look at your data than type at it, [CreamDB](https://www.creamdb.com) opens a MySQL workspace in a browser tab, with nothing to install.

- Browse databases, tables and views in a tree, and open any table in a grid where paging, sorting and filtering run in MySQL itself.
- Write SQL in an editor with autocomplete, formatting and history. Export results to CSV, JSON or Excel.
- See tables and their relations as an ER diagram, watch sessions and server variables in the Status panel, and review users and their grants on a read-only page.
- Ask in plain English, for example "total sales per month this year", and get the SQL, a result table and a chart.

The assistant only reads on its own. Any change becomes an approval card that you answer, and read-only mode blocks writes on the server. If your MySQL server is private, connect through an SSH tunnel.

It is free to start, and the same tab holds your PostgreSQL, MongoDB, Redis and 40+ other connections.

