
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 TABLEandALTER TABLEcommit 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
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, neverutf8. In MySQL, the nameutf8means an old three-byte encoding that cannot store emoji or some Asian characters.utf8mb4is 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
BIGINTworks better than a long string or a random UUID. - Money as whole cents, or use
DECIMAL(12,2). NeverFLOATorDOUBLE, which round. DATETIMEoverTIMESTAMPfor dates far in the future:TIMESTAMPstops 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.
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.
-- 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
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:
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:
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:
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
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
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. Useutf8mb4everywhere, including the connection settings. - Tables still on MyISAM. No transactions, and a crash can corrupt them. Check with
SHOW TABLE STATUS;and convert withALTER 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 TABLEoperations 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 withLIMIT. - Trusting
GROUP BYshortcuts. Selecting columns that are neither grouped nor aggregated returns arbitrary values on servers with a loosesql_mode. KeepONLY_FULL_GROUP_BYon.
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 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.
About the author
- CreamDB is a web-based database explorer with an AI assistant. Connect your databases, ask questions in plain English and get answers, tables and charts from any browser. Product news and tips here.
Comments
Join the discussionNo comments yet. Be the first to comment.



