An index is a sorted copy of some columns that lets MySQL 524 jump to matching rows instead of reading the whole table. It speeds up filters, joins, sorting and grouping, and slows every write. To show it, shop gets a million deterministic orders from a recursive CTE (Recursive CTEs) joined to itself:
CREATE TABLE big_orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL,
status ENUM('pending','paid','shipped','cancelled') NOT NULL,
ordered_at DATETIME NOT NULL, total DECIMAL(8,2) NOT NULL
);
INSERT INTO big_orders (customer_id, status, ordered_at, total)
WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 1000),
seq (n) AS (SELECT (a.n - 1) * 1000 + b.n FROM s AS a CROSS JOIN s AS b)
SELECT 1 + CRC32(CONCAT('c', n)) % 50000,
CASE WHEN n % 100 < 2 THEN 'pending' WHEN n % 100 < 6 THEN 'cancelled'
WHEN n % 100 < 26 THEN 'paid' ELSE 'shipped' END,
'2024-01-01' + INTERVAL n * 84 SECOND,
5 + CRC32(CONCAT('t', n)) % 20000 / 100
FROM seq ORDER BY n;Output
Query OK, 0 rows affected (0.049 sec) Query OK, 1000000 rows affected (7.611 sec) Records: 1000000 Duplicates: 0 Warnings: 0
That is 50,000 customers with about 20 orders each, 2% pending and 74% shipped. Timings come from this book's WSL2 6 machine (MySQL 9.7.2, a 128 MiB buffer pool shared with other databases), each taken after the table and indexes had been read once.