加前
-rw-rw---- 1 mysql mysql 8928 06-10 16:23 orders.frm
-rw-rw---- 1 mysql mysql 2000683008 06-10 16:27 orders.ibd
加后
-rw-rw---- 1 mysql mysql 8928 06-11 13:04 orders.frm
-rw-rw---- 1 mysql mysql 2315255808 06-11 13:09 orders.ibd
mysql> alter table orders add constraint orders_fk1 foreign key(o_custkey) references customer (c_custkey);
Query OK, 15000000 rows affected (4 min 58.88 sec)
Records: 15000000Duplicates: 0Warnings: 0
mysql>
mysql> explain select
->c_count,
->count(*) as custdist
-> from
->(
-> select
->c_custkey,
->count(o_orderkey)c_count
-> from
->customer left outer join orders on
-> c_custkey = o_custkey
-> and o_comment not like '%unusual%packages%'
-> group by
->c_custkey
->) c_orders
-> group by
->c_count
-> order by
->custdist desc,
->c_count desc limit10;
+----+-------------+------------+-------+---------------+------------+---------+--------------------------+---------+---------------------------------+
| id | select_type | table| type| possible_keys | key| key_len | ref
| rows| Extra
|
+----+-------------+------------+-------+---------------+------------+---------+--------------------------+---------+---------------------------------+
|1 | PRIMARY || ALL | NULL
| NULL | NULL| NULL
| 1500000 | Using temporary; Using filesort |
|2 | DERIVED | customer | index | NULL
| PRIMARY| 4 | NULL
|215781 | Using index
|
|2 | DERIVED | orders | ref | orders_fk1| orders_fk1 | 4 | tpch2.customer.c_custkey | 7 |
|
+----+-------------+------------+-------+---------------+------------+---------+--------------------------+---------+---------------------------------+
3 rows in set (1 min 29.45 sec)
mysql> select
->c_count,
->count(*) as custdist
-> from
->(
-> select
->c_custkey,
->count(o_orderkey)c_count
-> from
->customer left outer join orders on
-> c_custkey = o_custkey
-> and o_comment not like '%unusual%packages%'
-> group by
->c_custkey
->) c_orders
-> group by
->c_count
-> order by
->custdist desc,
->c_count desc limit10;
+---------+----------+
| c_count | custdist |
+---------+----------+
| 0 | 500023 |
|10 |65974 |
| 9 |65196 |
|11 |62247 |
| 8 |58373 |
|12 |55809 |
|13 |49853 |
| 7 |46762 |
|19 |46735 |
|18 |46216 |
+---------+----------+
10 rows in set (1 min 7.73 sec)
mysql>
|
