Let's say a page needs a customer's name and id. Somebody reaches for `SELECT *` because it's shorter, and every column crosses the network on every call, including a `bio` nobody will render.

A SELECT has three slots: the columns, the table, the condition. Name the columns, only the ones the page uses, and the engine ships exactly those. Months later somebody adds a heavy column, and your query doesn't even notice.

Name what you need. Nothing else comes along.

Let's say an order needs the buyer's email, and the email lives in users while the money lives in orders. One table holds half the answer. The join pairs rows that share a key: orders.user_id points at users.id.

The ON clause is the pairing rule, and it matters more than the keyword. INNER JOIN keeps only the pairs that match. And if you forget the ON entirely, MySQL pairs every row with every row, and a thousand orders become a million.

One shared key, one answer.

Let's say support asks you to deactivate one account. You type UPDATE users SET active = 0, your hand slips, Enter. Every user is now deactivated, and outside a transaction there's no undo.

The WHERE clause is the whole statement. Write it as a SELECT first and look at the rows it returns, then swap the verb. Inside a transaction you can ROLLBACK and breathe.

Say the WHERE out loud before you press Enter.

Let's say the logs table holds four million rows and none of them matter anymore. `DELETE FROM logs;` walks all four million, one at a time, firing triggers and writing undo for every row. TRUNCATE does not delete rows at all: it drops the table and re-creates it empty. Seconds instead of hours.

The trade is finality. It commits itself, fires no ON DELETE triggers, and cannot be rolled back. It reports "0 rows affected" because it has no count to give you, and it resets AUTO_INCREMENT back to its start.

Same empty table, from a different machine.

Let's say you're creating the orders table and you're in a hurry. Six months in, finance asks why the totals are off by a cent here and there. FLOAT is a binary fraction and it cannot hold 0.10 exactly, so rounding drift compounds.

This one statement decides all of it: the type per column, DECIMAL(10,2) for money, NOT NULL where a value must exist, and the keys. Every row that ever lands follows this shape.

The shape is cheap now and expensive later.

Let's say a query filters orders by user_id and there's no index there. Every query walks the whole table to find its handful of rows, and the table only grows.

An index is a sorted structure keyed on that column: the engine jumps straight to matching rows. The trade is real, though: reads get fast, every INSERT and UPDATE has one more structure to maintain, and the index takes disk. Index the columns your WHERE and JOIN actually use.

Fast reads, taxed writes. Both are true.

Let's say one page takes three seconds and you're fairly sure it's one query. You could add an index and hope, or you could put EXPLAIN in front of the query and read what the optimizer chose.

Three columns carry most of it. `type` is how the engine reaches the rows, and `ALL` means a full table scan, every row read. `key` is the index it picked, NULL means none. `rows` is its estimate of the work. On 8.0.18 or newer, EXPLAIN ANALYZE runs the query and shows actual rows and milliseconds beside the guesses.

Don't guess at slow. Read the plan.

Let's say the report needs the big orders, then the customers behind them, then a count over that. In old MySQL you nest subqueries three levels deep, parenthesized to the right edge of the screen, and nobody can change it safely.

WITH, new in MySQL 8, flips the order: name the middle step first, then query it by name like an ordinary table. The reader meets the definition before the question that uses it, the same way you'd explain it out loud. Same engine work, a query the next person can actually read.

Name it once, use it like a table.