Step-by-step articles on MySQL, part of our SQL collection.
Showing 10 articles
If your MySQL habits date from version 5.x, you're missing features that make queries simpler and schema changes safer. Common table expressions WITH monthly AS ( SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue FROM orders GROUP BY mont...
Most slow MySQL queries are missing an index or can't use the one that exists. EXPLAIN shows you which. Find slow queries SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5; -- seconds Or query performance_schema.events_statements_summary_by_di...
Duplicate rows creep in through double-clicked buttons, retried imports and missing constraints. Here's how to find them, clean them up and stop them coming back. 1. Find duplicates SELECT email, COUNT(*) AS copies FROM subscribers GROUP BY email HAVING COUNT(...
MySQL's JSON column type validates documents on insert and stores them in an efficient binary format. It's handy for flexible attributes alongside normal columns. Storing and reading CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name V...
"Deadlock found when trying to get lock; try restarting transaction" is one of the most common MySQL errors in busy apps. It's not a bug in MySQL; it's two transactions waiting on each other. How a deadlock happens Transaction A Transaction B UPDATE accounts ....
Both are mature, fast, open-source relational databases that run some of the biggest sites in the world. For most applications either will work; the differences show up at the edges. Where PostgreSQL leads Richer SQL and types : arrays, ranges, JSONB with powe...
LIMIT 20 OFFSET 100000 looks innocent, but MySQL must read and throw away 100,000 rows to return 20. Deep pages get slower and slower. The problem SELECT id, title, created_at FROM posts ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000; -- reads 100,02...
On a table with hundreds of millions of rows, the wrong ALTER TABLE can lock writes for hours. MySQL offers several ways to avoid that. 1. Ask for INSTANT ALTER TABLE orders ADD COLUMN gift_message VARCHAR(255) NULL, ALGORITHM=INSTANT; Instant changes only tou...
MySQL 8.0 reached end of life in April 2026, so it no longer receives security fixes. The current long-term support line is MySQL 8.4 LTS . The new release model LTS releases (8.4) - long support with bug and security fixes only. The right choice for productio...
A backup you've never restored is a hope, not a backup. Here's how to take consistent MySQL backups and prove they work. Logical backups with mysqldump mysqldump --single-transaction --routines --triggers --events \ --set-gtid-purged=OFF -h db -u backup -p sho...
