MySQL

Why is this query slow

EXPLAIN SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01';

-- MySQL 8: the real execution plan, with actual times and row counts
EXPLAIN ANALYZE SELECT ...;

Four columns carry nearly all the signal:

• type — ALL means a full table scan. ref or range means an index is being used. Going from ALL to ref is usually the whole fix.
• rows — how many MySQL expects to examine. Compare it with how many you expect back. A gap of several orders of magnitude is the problem.
• key — which index was chosen, or NULL for none.
• Extra — Using filesort and Using temporary both mean work on disk that an index could have avoided.

Indexes

SHOW INDEX FROM orders;

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

-- Enforce uniqueness and get an index in the same move
CREATE UNIQUE INDEX idx_users_email ON users (email);

DROP INDEX idx_old_thing ON orders;

Column order in a composite index is not cosmetic. An index on (customer_id, created_at) serves a query filtering on customer_id, or on both — but not one filtering only on created_at. Leftmost prefix.

Three things stop an index being used, and all three look innocent:

WHERE YEAR(created_at) = 2026        -- function on the column: no index
WHERE created_at >= '2026-01-01'     -- rewrite as a range: index used

WHERE customer_id = '42'             -- string against an int column
WHERE customer_id = 42               -- match the type

WHERE name LIKE '%smith'             -- leading wildcard: no index
WHERE name LIKE 'smith%'             -- index used

Joins, and the row count surprise

-- Only rows with a match on both sides
SELECT ... FROM a INNER JOIN b ON b.a_id = a.id

-- Every row of a, with nulls where b has no match
SELECT ... FROM a LEFT JOIN b ON b.a_id = a.id

-- Rows of a with NO match in b - the anti-join
SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL

-- Join on a condition rather than a foreign key
SELECT ... FROM a JOIN b ON b.a_id = a.id AND b.status = 'active'

A join multiplies rows. One order with three line items returns three rows, and SUM(orders.total) across that join triples the money. If a total is suddenly wrong by an exact multiple, this is why. Aggregate in a subquery, or use SUM(DISTINCT ...) only when you understand what it discards.

ON and WHERE differ on a LEFT JOIN. A condition in WHERE filters after the join and turns it back into an inner join; the same condition in ON keeps the unmatched rows.

Grouping and windows

SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spend
FROM orders
WHERE created_at > '2026-01-01'     -- filters BEFORE grouping
GROUP BY customer_id
HAVING spend > 500                  -- filters AFTER grouping
ORDER BY spend DESC;

-- Window functions (MySQL 8): aggregate without collapsing rows
SELECT
  customer_id,
  total,
  SUM(total) OVER (PARTITION BY customer_id) AS customer_total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS n
FROM orders;

WHERE before grouping, HAVING after. Putting a plain column filter in HAVING works and scans far more rows than it needed to.

Everyday admin

-- What is running right now
SHOW FULL PROCESSLIST;
KILL 1234;              -- the id from the first column

-- Table sizes, largest first
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

-- What is holding a lock (MySQL 8)
SELECT * FROM performance_schema.data_locks;

SHOW CREATE TABLE orders;    -- the real definition, including indexes

Dump and restore

# --single-transaction avoids locking the tables on InnoDB, so the site
# stays up while the dump runs. Without it, a large dump is an outage.
mysqldump --single-transaction --quick --routines --triggers \
  -u user -p dbname > dump.sql

# Schema only, or data only
mysqldump --no-data -u user -p dbname > schema.sql
mysqldump --no-create-info -u user -p dbname > data.sql

mysql -u user -p dbname < dump.sql

--single-transaction and --quick are the two that matter on anything live: the first keeps the site serving, the second streams rows instead of buffering the whole table into memory.