MySQL のクエリが遅いのはなぜ?

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 843201 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

EXPLAIN SELECT * FROM orders FORCE INDEX (idx_created_at) WHERE customer_id = 42;

-- Uses the composite index efficiently
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

-- Can't use the composite index — status isn't the leading column
SELECT * FROM orders WHERE status = 'pending';

-- May require a table row lookup for each match
SELECT * FROM orders WHERE customer_id = 42;

-- Can use a covering index on (customer_id, status, total)
SELECT status, total FROM orders WHERE customer_id = 42;

-- Can't use an index on created_at
WHERE YEAR(created_at) = 2024

-- Can use an index on created_at
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01';

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- log queries taking more than 1 second

SHOW VARIABLES LIKE 'slow_query_log_file';

mysqldumpslow -s t /path/to/slow-query.log

SHOW PROCESSLIST;

SHOW ENGINE INNODB STATUS;