Skip to content

Instantly share code, notes, and snippets.

@sh2

sh2/order_by.sql

Created October 20, 2013 16:16
Show Gist options
  • Save sh2/7071702 to your computer and use it in GitHub Desktop.
Save sh2/7071702 to your computer and use it in GitHub Desktop.
Download ZIP
ORDER BY狙いのインデックス
Raw
order_by.sql
CREATE TABLE `customer` (
`c_id` int(11) NOT NULL,
`c_d_id` tinyint(4) NOT NULL,
`c_w_id` smallint(6) NOT NULL,
`c_first` varchar(16) DEFAULT NULL,
`c_middle` char(2) DEFAULT NULL,
`c_last` varchar(16) DEFAULT NULL,
`c_street_1` varchar(20) DEFAULT NULL,
`c_street_2` varchar(20) DEFAULT NULL,
`c_city` varchar(20) DEFAULT NULL,
`c_state` char(2) DEFAULT NULL,
`c_zip` char(9) DEFAULT NULL,
`c_phone` char(16) DEFAULT NULL,
`c_since` datetime DEFAULT NULL,
`c_credit` char(2) DEFAULT NULL,
`c_credit_lim` bigint(20) DEFAULT NULL,
`c_discount` decimal(4,2) DEFAULT NULL,
`c_balance` decimal(12,2) DEFAULT NULL,
`c_ytd_payment` decimal(12,2) DEFAULT NULL,
`c_payment_cnt` smallint(6) DEFAULT NULL,
`c_delivery_cnt` smallint(6) DEFAULT NULL,
`c_data` text,
PRIMARY KEY (`c_w_id`,`c_d_id`,`c_id`),
KEY `idx_customer` (`c_w_id`,`c_d_id`,`c_last`,`c_first`),
CONSTRAINT `fkey_customer_1` FOREIGN KEY (`c_w_id`, `c_d_id`) REFERENCES `district` (`d_w_id`, `d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `orders` (
`o_id` int(11) NOT NULL,
`o_d_id` tinyint(4) NOT NULL,
`o_w_id` smallint(6) NOT NULL,
`o_c_id` int(11) DEFAULT NULL,
`o_entry_d` datetime DEFAULT NULL,
`o_carrier_id` tinyint(4) DEFAULT NULL,
`o_ol_cnt` tinyint(4) DEFAULT NULL,
`o_all_local` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`),
KEY `idx_orders` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`),
CONSTRAINT `fkey_orders_1` FOREIGN KEY (`o_w_id`, `o_d_id`, `o_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> SELECT COUNT(*) FROM customer;
+----------+
| COUNT(*) |
+----------+
| 3000000 |
+----------+
1 row in set (0.72 sec)
mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
| 3000000 |
+----------+
1 row in set (0.65 sec)
複合主キーなのでめんどくさいですが我慢してください。
■そのまま
EXPLAIN
SELECT c.c_first, o.o_id
FROM customer c
INNER JOIN orders o
ON c.c_w_id = o.o_w_id AND c.c_d_id = o.o_w_id AND c.c_id = o.o_c_id
ORDER BY c.c_first LIMIT 10;
+----+-------------+-------+--------+----------------------+------------+---------+-------------------------------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------+------------+---------+-------------------------------------------+---------+-----------------------------------------------------------+
| 1 | SIMPLE | o | index | PRIMARY,idx_orders | idx_orders | 12 | NULL | 3017932 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY,idx_customer | PRIMARY | 7 | tpcc.o.o_w_id,tpcc.o.o_w_id,tpcc.o.o_c_id | 1 | Using where |
+----+-------------+-------+--------+----------------------+------------+---------+-------------------------------------------+---------+-----------------------------------------------------------+
+--------------+------+
| c_first | o_id |
+--------------+------+
| 009RaoU6p8ZL | 344 |
| 009RaoU6p8ZL | 2385 |
| 009RaoU6p8ZL | 1946 |
| 009RaoU6p8ZL | 806 |
| 009RaoU6p8ZL | 2810 |
| 009RaoU6p8ZL | 1292 |
| 009RaoU6p8ZL | 2300 |
| 009RaoU6p8ZL | 2 |
| 009RaoU6p8ZL | 2535 |
| 009RaoU6p8ZL | 95 |
+--------------+------+
10 rows in set (7.96 sec)
■どうもおかしいのでSTRAIGHT_JOINをつけてcustomerを駆動表にする
EXPLAIN
SELECT STRAIGHT_JOIN c.c_first, o.o_id
FROM customer c
INNER JOIN orders o
ON c.c_w_id = o.o_w_id AND c.c_d_id = o.o_w_id AND c.c_id = o.o_c_id
ORDER BY c.c_first LIMIT 10;
+----+-------------+-------+-------+----------------------+--------------+---------+---------------+---------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------+--------------+---------+---------------+---------+------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY,idx_customer | idx_customer | 137 | NULL | 2883818 | Using where; Using index; Using filesort |
| 1 | SIMPLE | o | ref | PRIMARY,idx_orders | idx_orders | 2 | tpcc.c.c_d_id | 15883 | Using where; Using index |
+----+-------------+-------+-------+----------------------+--------------+---------+---------------+---------+------------------------------------------+
+--------------+------+
| c_first | o_id |
+--------------+------+
| 009RaoU6p8ZL | 2810 |
| 009RaoU6p8ZL | 1946 |
| 009RaoU6p8ZL | 95 |
| 009RaoU6p8ZL | 2385 |
| 009RaoU6p8ZL | 1292 |
| 009RaoU6p8ZL | 806 |
| 009RaoU6p8ZL | 2300 |
| 009RaoU6p8ZL | 344 |
| 009RaoU6p8ZL | 2 |
| 009RaoU6p8ZL | 2535 |
+--------------+------+
10 rows in set (6.33 sec)
■マテリアライズ
CREATE TABLE c_sort (
`c_id` int(11) NOT NULL,
`c_d_id` tinyint(4) NOT NULL,
`c_w_id` smallint(6) NOT NULL,
`c_first` varchar(16) DEFAULT NULL,
PRIMARY KEY (`c_w_id`,`c_d_id`,`c_id`),
KEY c_sort_ix1 (`c_first`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO c_sort SELECT c_id, c_d_id, c_w_id, c_first FROM customer;
EXPLAIN
SELECT STRAIGHT_JOIN c.c_first, o.o_id
FROM (SELECT c_id, c_d_id, c_w_id, c_first FROM c_sort ORDER BY c_first) c
INNER JOIN orders o
ON c.c_w_id = o.o_w_id AND c.c_d_id = o.o_w_id AND c.c_id = o.o_c_id
ORDER BY c.c_first LIMIT 10;
+----+-------------+------------+-------+--------------------+------------+---------+----------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------+------------+---------+----------+---------+-----------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2991046 | Using where; Using filesort |
| 1 | PRIMARY | o | ref | PRIMARY,idx_orders | idx_orders | 2 | c.c_w_id | 15883 | Using where; Using index |
| 2 | DERIVED | c_sort | index | NULL | c_sort_ix1 | 67 | NULL | 2991046 | Using index |
+----+-------------+------------+-------+--------------------+------------+---------+----------+---------+-----------------------------+
+--------------+------+
| c_first | o_id |
+--------------+------+
| 009RaoU6p8ZL | 2810 |
| 009RaoU6p8ZL | 1946 |
| 009RaoU6p8ZL | 95 |
| 009RaoU6p8ZL | 2385 |
| 009RaoU6p8ZL | 1292 |
| 009RaoU6p8ZL | 806 |
| 009RaoU6p8ZL | 2300 |
| 009RaoU6p8ZL | 344 |
| 009RaoU6p8ZL | 2 |
| 009RaoU6p8ZL | 2535 |
+--------------+------+
10 rows in set (2.43 sec)
■マテリアライズして外側のORDER BYをカット
EXPLAIN
SELECT STRAIGHT_JOIN c.c_first, o.o_id
FROM (SELECT c_id, c_d_id, c_w_id, c_first FROM c_sort ORDER BY c_first) c
INNER JOIN orders o
ON c.c_w_id = o.o_w_id AND c.c_d_id = o.o_w_id AND c.c_id = o.o_c_id
LIMIT 10;
+----+-------------+------------+-------+--------------------+------------+---------+----------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+--------------------+------------+---------+----------+---------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2991046 | Using where |
| 1 | PRIMARY | o | ref | PRIMARY,idx_orders | idx_orders | 2 | c.c_w_id | 15883 | Using where; Using index |
| 2 | DERIVED | c_sort | index | NULL | c_sort_ix1 | 67 | NULL | 2991046 | Using index |
+----+-------------+------------+-------+--------------------+------------+---------+----------+---------+--------------------------+
一応同じ結果に。
+--------------+------+
| c_first | o_id |
+--------------+------+
| 009RaoU6p8ZL | 2810 |
| 009RaoU6p8ZL | 1946 |
| 009RaoU6p8ZL | 95 |
| 009RaoU6p8ZL | 2385 |
| 009RaoU6p8ZL | 1292 |
| 009RaoU6p8ZL | 806 |
| 009RaoU6p8ZL | 2300 |
| 009RaoU6p8ZL | 344 |
| 009RaoU6p8ZL | 2 |
| 009RaoU6p8ZL | 2535 |
+--------------+------+
10 rows in set (2.23 sec)
■ORDER BY狙いのインデックス
ALTER TABLE customer ADD KEY customer_ix1 (c_first);
EXPLAIN
SELECT c.c_first, o.o_id
FROM customer c FORCE INDEX (customer_ix1)
INNER JOIN orders o
ON c.c_w_id = o.o_w_id AND c.c_d_id = o.o_w_id AND c.c_id = o.o_c_id
ORDER BY c.c_first LIMIT 10;
FORCE INDEXないと最初のプランのままでした。
+----+-------------+-------+-------+--------------------+--------------+---------+---------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+--------------+---------+---------------+-------+--------------------------+
| 1 | SIMPLE | c | index | NULL | customer_ix1 | 67 | NULL | 1 | Using where; Using index |
| 1 | SIMPLE | o | ref | PRIMARY,idx_orders | idx_orders | 2 | tpcc.c.c_d_id | 15883 | Using where; Using index |
+----+-------------+-------+-------+--------------------+--------------+---------+---------------+-------+--------------------------+
+--------------+------+
| c_first | o_id |
+--------------+------+
| 009RaoU6p8ZL | 2810 |
| 009RaoU6p8ZL | 1946 |
| 009RaoU6p8ZL | 95 |
| 009RaoU6p8ZL | 2385 |
| 009RaoU6p8ZL | 1292 |
| 009RaoU6p8ZL | 806 |
| 009RaoU6p8ZL | 2300 |
| 009RaoU6p8ZL | 344 |
| 009RaoU6p8ZL | 2 |
| 009RaoU6p8ZL | 2535 |
+--------------+------+
10 rows in set (0.01 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment