MySQL のカバリングインデックス

CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    status     VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL
);

CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- All three columns in the SELECT are in the index
EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 42;

+-------+---------------------------+------+-------------+
| type  | key                       | rows | Extra       |
+-------+---------------------------+------+-------------+
| ref   | idx_user_status_created   |   12 | Using index |
+-------+---------------------------+------+-------------+

-- order_total is not in the index
EXPLAIN SELECT user_id, status, created_at, order_total FROM orders WHERE user_id = 42;

+-------+---------------------------+------+-------+
| type  | key                       | rows | Extra |
+-------+---------------------------+------+-------+
| ref   | idx_user_status_created   |   12 | NULL  |
+-------+---------------------------+------+-------+

-- Query pattern:
SELECT status, created_at FROM orders
WHERE user_id = 42
ORDER BY created_at;

-- Covering index: equality column first, then sort column, then SELECT columns
CREATE INDEX idx_covering ON orders (user_id, created_at, status);

-- Not a candidate: SELECT * means no index can cover this
SELECT * FROM orders WHERE user_id = 42;

-- Good candidate: narrow SELECT, high-traffic query
SELECT id, status FROM orders WHERE user_id = 42 AND created_at > '2024-01-01';