mysql 5.5 tpc-h测试

〇〇

mysql> explain
    -> -- 7
    -> select
    ->  supp_nation,
    ->  cust_nation,
    ->  l_year,
    ->  sum(volume) as revenue
    -> from
    ->  (
    ->   select
    ->    n1.n_name as supp_nation,
    ->    n2.n_name as cust_nation,
    ->    extract(year from l_shipdate) as l_year,
    ->    l_extendedprice * (1 - l_discount) as volume
    ->   from
    ->    supplier,
    ->    lineitem,
    ->    orders,
    ->    customer,
    ->    nation n1,
    ->    nation n2
    ->   where
    ->    s_suppkey = l_suppkey
    ->    and o_orderkey = l_orderkey
    ->    and c_custkey = o_custkey
    ->    and s_nationkey = n1.n_nationkey
    ->    and c_nationkey = n2.n_nationkey
    ->    and (
    ->     (n1.n_name = 'UNITED KINGDOM' and n2.n_name = 'ROMANIA')
    ->     or (n1.n_name = 'ROMANIA' and n2.n_name = 'UNITED KINGDOM')
    ->    )
    ->    and l_shipdate between date '1995-01-01' and date '1996-12-31'
    ->  ) shipping
    -> group by
    ->  supp_nation,
    ->  cust_nation,
    ->  l_year
    -> order by
    ->  supp_nation,
    ->  cust_nation,
    ->  l_year limit  10;
+----+-------------+------------+--------+----------------------+--------------+---------+---------------------------+-------+---------------------------------+
| id | select_type | table      | type   | possible_keys        | key          | key_len | ref                       | rows  | Extra                           |
+----+-------------+------------+--------+----------------------+--------------+---------+---------------------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL         | NULL    | NULL                      | 58367 | Using temporary; Using filesort |
|  2 | DERIVED     | n2         | ALL    | PRIMARY              | NULL         | NULL    | NULL                      |    25 | Using where                     |
|  2 | DERIVED     | customer   | ref    | PRIMARY,customer_fk1 | customer_fk1 | 4       | tpch.n2.n_nationkey       |  4404 | Using index                     |
|  2 | DERIVED     | orders     | ref    | PRIMARY,orders_fk1   | orders_fk1   | 4       | tpch.customer.c_custkey   |     7 | Using index                     |
|  2 | DERIVED     | lineitem   | ref    | PRIMARY              | PRIMARY      | 4       | tpch.orders.o_orderkey    |     2 | Using where                     |
|  2 | DERIVED     | supplier   | eq_ref | PRIMARY,supplier_fk1 | PRIMARY      | 4       | tpch.lineitem.l_suppkey   |     1 |                                 |
|  2 | DERIVED     | n1         | eq_ref | PRIMARY              | PRIMARY      | 4       | tpch.supplier.s_nationkey |     1 | Using where                     |
+----+-------------+------------+--------+----------------------+--------------+---------+---------------------------+-------+---------------------------------+
7 rows in set (26.26 sec)

mysql>
mysql> -- 7
mysql> select
    ->  supp_nation,
    ->  cust_nation,
    ->  l_year,
    ->  sum(volume) as revenue
    -> from
    ->  (
    ->   select
    ->    n1.n_name as supp_nation,
    ->    n2.n_name as cust_nation,
    ->    extract(year from l_shipdate) as l_year,
    ->    l_extendedprice * (1 - l_discount) as volume
    ->   from
    ->    supplier,
    ->    lineitem,
    ->    orders,
    ->    customer,
    ->    nation n1,
    ->    nation n2
    ->   where
    ->    s_suppkey = l_suppkey
    ->    and o_orderkey = l_orderkey
    ->    and c_custkey = o_custkey
    ->    and s_nationkey = n1.n_nationkey
    ->    and c_nationkey = n2.n_nationkey
    ->    and (
    ->     (n1.n_name = 'UNITED KINGDOM' and n2.n_name = 'ROMANIA')
    ->     or (n1.n_name = 'ROMANIA' and n2.n_name = 'UNITED KINGDOM')
    ->    )
    ->    and l_shipdate between date '1995-01-01' and date '1996-12-31'
    ->  ) shipping
    -> group by
    ->  supp_nation,
    ->  cust_nation,
    ->  l_year
    -> order by
    ->  supp_nation,
    ->  cust_nation,
    ->  l_year limit  10;
+----------------+----------------+--------+----------------+
| supp_nation    | cust_nation    | l_year | revenue        |
+----------------+----------------+--------+----------------+
| ROMANIA        | UNITED KINGDOM |   1995 | 529821664.1735 |
| ROMANIA        | UNITED KINGDOM |   1996 | 541319833.0274 |
| UNITED KINGDOM | ROMANIA        |   1995 | 528819804.2166 |
| UNITED KINGDOM | ROMANIA        |   1996 | 525485490.0643 |
+----------------+----------------+--------+----------------+
4 rows in set (25.94 sec)

mysql>

〇〇

mysql> explain -- 8
    -> select
    ->  o_year,
    ->  sum(case
    ->   when nation = 'ROMANIA' then volume
    ->   else 0
    ->  end) / sum(volume) as mkt_share
    -> from
    ->  (
    ->   select
    ->    extract(year from o_orderdate) as o_year,
    ->    l_extendedprice * (1 - l_discount) as volume,
    ->    n2.n_name as nation
    ->   from
    ->    part,
    ->    supplier,
    ->    lineitem,
    ->    orders,
    ->    customer,
    ->    nation n1,
    ->    nation n2,
    ->    region
    ->   where
    ->    p_partkey = l_partkey
    ->    and s_suppkey = l_suppkey
    ->    and l_orderkey = o_orderkey
    ->    and o_custkey = c_custkey
    ->    and c_nationkey = n1.n_nationkey
    ->    and n1.n_regionkey = r_regionkey
    ->    and r_name = 'EUROPE'
    ->    and s_nationkey = n2.n_nationkey
    ->    and o_orderdate between date '1995-01-01' and date '1996-12-31'
    ->    and p_type = 'STANDARD BURNISHED STEEL'
    ->  ) all_nations
    -> group by
    ->  o_year
    -> order by
    ->  o_year limit  10;
+----+-------------+------------+--------+----------------------+--------------+---------+---------------------------+-------+---------------------------------+
| id | select_type | table      | type   | possible_keys        | key          | key_len | ref                       | rows  | Extra                           |
+----+-------------+------------+--------+----------------------+--------------+---------+---------------------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL         | NULL    | NULL                      | 24312 | Using temporary; Using filesort |
|  2 | DERIVED     | region     | ALL    | PRIMARY              | NULL         | NULL    | NULL                      |     5 | Using where                     |
|  2 | DERIVED     | n1         | ref    | PRIMARY,nation_fk1   | nation_fk1   | 4       | tpch.region.r_regionkey   |     2 | Using index                     |
|  2 | DERIVED     | customer   | ref    | PRIMARY,customer_fk1 | customer_fk1 | 4       | tpch.n1.n_nationkey       |  4404 | Using index                     |
|  2 | DERIVED     | orders     | ref    | PRIMARY,orders_fk1   | orders_fk1   | 4       | tpch.customer.c_custkey   |     7 | Using where                     |
|  2 | DERIVED     | lineitem   | ref    | PRIMARY              | PRIMARY      | 4       | tpch.orders.o_orderkey    |     2 |                                 |
|  2 | DERIVED     | part       | eq_ref | PRIMARY              | PRIMARY      | 4       | tpch.lineitem.l_partkey   |     1 | Using where                     |
|  2 | DERIVED     | supplier   | eq_ref | PRIMARY,supplier_fk1 | PRIMARY      | 4       | tpch.lineitem.l_suppkey   |     1 |                                 |
|  2 | DERIVED     | n2         | eq_ref | PRIMARY              | PRIMARY      | 4       | tpch.supplier.s_nationkey |     1 |                                 |
+----+-------------+------------+--------+----------------------+--------------+---------+---------------------------+-------+---------------------------------+
9 rows in set (35.01 sec)

mysql> -- 8
mysql> select
    ->  o_year,
    ->  sum(case
    ->   when nation = 'ROMANIA' then volume
    ->   else 0
    ->  end) / sum(volume) as mkt_share
    -> from
    ->  (
    ->   select
    ->    extract(year from o_orderdate) as o_year,
    ->    l_extendedprice * (1 - l_discount) as volume,
    ->    n2.n_name as nation
    ->   from
    ->    part,
    ->    supplier,
    ->    lineitem,
    ->    orders,
    ->    customer,
    ->    nation n1,
    ->    nation n2,
    ->    region
    ->   where
    ->    p_partkey = l_partkey
    ->    and s_suppkey = l_suppkey
    ->    and l_orderkey = o_orderkey
    ->    and o_custkey = c_custkey
    ->    and c_nationkey = n1.n_nationkey
    ->    and n1.n_regionkey = r_regionkey
    ->    and r_name = 'EUROPE'
    ->    and s_nationkey = n2.n_nationkey
    ->    and o_orderdate between date '1995-01-01' and date '1996-12-31'
    ->    and p_type = 'STANDARD BURNISHED STEEL'
    ->  ) all_nations
    -> group by
    ->  o_year
    -> order by
    ->  o_year limit  10;
+--------+------------+
| o_year | mkt_share  |
+--------+------------+
|   1995 | 0.03780949 |
|   1996 | 0.03851161 |
+--------+------------+
2 rows in set (31.83 sec)

mysql>

〇〇

http://www.mysqlperformanceblog.com/2008/04/10/tpc-h-run-on-mysql-51-and-60/
TPC-H Run on MySQL 5.1 and 6.0April 10, 2008 By Peter Zaitsev 15 Comments
We were doing MySQL Performance evaluation on TPC-H queries for the client and they kindly allowed us to publish results which are very interesting.
This is obviously not audited TPC-H run, and it can’t be because we used MyISAM tables which are not ACID complaint. Plus we only measured Power to keep things simple.
We tested 10G and 100G data sets which was CPU bound and IO bound box on the Dell 2950 box w 16G of RAM which we used for testing. Even though box had 8 cores it is little use for MySQL as only one query is ran concurrently, same can be told about 8 hard drives which this box had.
MySQL Also was very slow running some queries so we changed scripts a bit to kill extremely long running queries to get results for others this means we can’t really get a valid TPC-H result from MySQL,though at least we get to see performance of individual queries.
We also packaged the toolset we used for benchmarks so you can repeat them if you like. It can be downloaded here


So let us first take a look at MySQL 5.1.23 vs 6.0.4 results for 10GB data set which “fits in memory”. The cut-off time for this test was 1 hour so query taking over 1 hour has NA. Times are given in seconds.
Ratio is MySQL 6.0 time divided by MySQL 5.1 time so if it is less than 1 MySQL 6.0 is faster if more than 1 slower:

Query	MySQL 5.0.23	MySQL 6.0.4	Ratio
Query1	370.50	372.16	1.00
Query2	724.63	623.68	0.86
Query3	328.25	354.62	1.08
Query4	7.95	8.08	1.02
Query5	154.36	161.65	1.05
Query6	29.35	38.51	1.31
Query7	322.04	331.05	1.03
Query8	469.31	457.18	0.97
Query9	123.75	121.33	0.98
Query10	343.37	341.23	0.99
Query11	2.32	2.34	1.01
Query12	28.68	38.61	1.35
Query13	46.00	47.84	1.04
Query14	8.83	8.23	0.93
Query15	21.50	23.61	1.10
Query16	55.48	61.42	1.11
Query17	9.00	9.33	1.04
Query18	N/A	1962.96	N/A
Query19	5.03	5.30	1.05
Query20	2.06	0.26	0.13
Query21	33.16	32.98	0.99
Query22	7.84	8.06	1.03

〇〇

mysql> explain -- 10
    -> select
    ->  c_custkey,
    ->  c_name,
    ->  sum(l_extendedprice * (1 - l_discount)) as revenue,
    ->  c_acctbal,
    ->  n_name,
    ->  c_address,
    ->  c_phone,
    ->  c_comment
    -> from
    ->  customer,
    ->  orders,
    ->  lineitem,
    ->  nation
    -> where
    ->  c_custkey = o_custkey
    ->  and l_orderkey = o_orderkey
    ->  and o_orderdate >= date '1994-05-01'
    ->  and o_orderdate < date '1994-05-01' + interval '3' month
    ->  and l_returnflag = 'R'
    ->  and c_nationkey = n_nationkey
    -> group by
    ->  c_custkey,
    ->  c_name,
    ->  c_acctbal,
    ->  c_phone,
    ->  n_name,
    ->  c_address,
    ->  c_comment
    -> order by
    ->  revenue desc limit  20;
+----+-------------+----------+------+----------------------+--------------+---------+-------------------------+------+---------------------------------+
| id | select_type | table    | type | possible_keys        | key          | key_len | ref                     | rows | Extra                           |
+----+-------------+----------+------+----------------------+--------------+---------+-------------------------+------+---------------------------------+
|  1 | SIMPLE      | nation   | ALL  | PRIMARY              | NULL         | NULL    | NULL                    |   25 | Using temporary; Using filesort |
|  1 | SIMPLE      | customer | ref  | PRIMARY,customer_fk1 | customer_fk1 | 4       | tpch.nation.n_nationkey | 4404 |                                 |
|  1 | SIMPLE      | orders   | ref  | PRIMARY,orders_fk1   | orders_fk1   | 4       | tpch.customer.c_custkey |    7 | Using where                     |
|  1 | SIMPLE      | lineitem | ref  | PRIMARY              | PRIMARY      | 4       | tpch.orders.o_orderkey  |    2 | Using where                     |
+----+-------------+----------+------+----------------------+--------------+---------+-------------------------+------+---------------------------------+
4 rows in set (0.00 sec)

mysql> -- 10
mysql> select
    ->  c_custkey,
    ->  c_name,
    ->  sum(l_extendedprice * (1 - l_discount)) as revenue,
    ->  c_acctbal,
    ->  n_name,
    ->  c_address,
    ->  c_phone,
    ->  c_comment
    -> from
    ->  customer,
    ->  orders,
    ->  lineitem,
    ->  nation
    -> where
    ->  c_custkey = o_custkey
    ->  and l_orderkey = o_orderkey
    ->  and o_orderdate >= date '1994-05-01'
    ->  and o_orderdate < date '1994-05-01' + interval '3' month
    ->  and l_returnflag = 'R'
    ->  and c_nationkey = n_nationkey
    -> group by
    ->  c_custkey,
    ->  c_name,
    ->  c_acctbal,
    ->  c_phone,
    ->  n_name,
    ->  c_address,
    ->  c_comment
    -> order by
    ->  revenue desc limit  20;
+-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+---------------------------------------------------------------------------------------------------------+
| c_custkey | c_name             | revenue     | c_acctbal | n_name         | c_address                                | c_phone         | c_comment                                                                                               |
+-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+---------------------------------------------------------------------------------------------------------+
|    859087 | Customer#000859087 | 795591.1319 |   4009.94 | PERU           | i VYSwydv M5ss0b                         | 27-458-775-5124 | the enticingly regular packages. fluffily                                                               |
|    740428 | Customer#000740428 | 774058.5652 |   8832.45 | UNITED KINGDOM | f6GR6VnLK,yXj8mxGUIwXBh6R                | 33-486-888-6888 | ual, even dependencies are. s                                                                           |
|   1121018 | Customer#001121018 | 771017.7419 |    440.07 | FRANCE         | kymXkSpb2QJwM0fyG5akNjEpABQ6KMVL         | 16-979-738-3029 |  gifts. blithely final pinto beans above the ironic requests cajole after the spe                       |
|    940558 | Customer#000940558 | 757257.8295 |   9296.89 | IRAQ           | rbUTQJaBKK                               | 21-412-697-1127 | l accounts use. quickly ironic platelets according to the bold courts use fluffily along                |
|    626548 | Customer#000626548 | 741637.4629 |   8816.29 | CANADA         | wC,cqFOqmayHMp5wsz9                      | 13-108-148-1920 | ites haggle carefully quickly final packages. slyly unusual instructions are. furiously pending depos   |
|    176566 | Customer#000176566 | 740436.4792 |   6653.77 | IRAQ           | P6cScNofcMOgN                            | 21-481-935-8112 | nts. even platelets about the slyly pending theodoli                                                    |
|    398209 | Customer#000398209 | 737423.6546 |   8078.60 | BRAZIL         | Np9BZBmhKjnV2RyVJDRu3VN2                 | 12-677-416-7086 | s requests. carefully idle pinto beans impress. regular, ironic deposits cajole final deposits. furious |
|    105427 | Customer#000105427 | 733099.2779 |   1913.89 | FRANCE         | VNa1DaHYUaS5j                            | 16-588-422-6089 | e blithely instructions. ironic tithes sleep furiously through the fluf                                 |
|   1427804 | Customer#001427804 | 726550.3773 |   5641.37 | ETHIOPIA       | w24QdAVFSER                              | 15-973-207-5120 |  boost slyly alongside of the daring deposits. dependencies us                                          |
|    365584 | Customer#000365584 | 721747.2564 |   7085.64 | CANADA         | LsxxTCJ2nGrptdL                          | 13-881-145-1128 | s packages. slyly regular patterns boost bold, unusual accounts.                                        |
|   1391057 | Customer#001391057 | 707403.3930 |   -956.13 | ETHIOPIA       | wU4WfTvm3zsH99                           | 15-744-116-1290 | tes use waters. fluffily special ideas along the regular, ironic                                        |
|   1081033 | Customer#001081033 | 697514.1760 |   8912.66 | VIETNAM        | kc17y n5axGKI7vtZZYyPN, 3KURHDzNL5snT    | 31-738-534-2032 | he fluffily regular pinto beans. deposits detect thinly.                                                |
|    744874 | Customer#000744874 | 696698.6251 |    269.30 | CHINA          | J6yHXhjiLifQYl6S, wNzAWj9QmY4            | 28-380-324-8315 | ld dugouts nag furiously after the always unusual theodolites. quickly unusual                          |
|   1177250 | Customer#001177250 | 695476.5153 |   -622.72 | ROMANIA        | qGZWM0sT8ZUXZ                            | 29-104-754-1207 | dolites sleep quickly ironic packages. silent packages haggle carefully                                 |
|    901693 | Customer#000901693 | 694203.9166 |   4822.73 | VIETNAM        | o0EVo9buBfc5ZY,gi4VFvXjWR,qJNhjPHXvx mtY | 31-623-126-9286 | ages sleep above the slyly regular requests. carefully even theodolites after the special, bold accoun  |
|   1478164 | Customer#001478164 | 692618.5550 |   9128.79 | IRAN           | 2vy4nYJ,WD y0uz7uMvtcRmT2D8HSt4S9CbF     | 20-455-516-1410 | usly furiously regular pinto beans. blithely i                                                          |
|    236758 | Customer#000236758 | 691176.2952 |   8416.76 | ROMANIA        | z eZsbos p u                             | 29-844-815-1064 | eep furiously: pending deposits boost carefully even, final excuses. even, pendi                        |
|    553184 | Customer#000553184 | 690088.7148 |   5826.55 | SAUDI ARABIA   | ir9Q2bjnZilhFoS8gH4I3                    | 30-599-126-7708 | lyly thin forges cajole slyly? regular deposits according to the special, express notornis cajole       |
|   1459033 | Customer#001459033 | 683803.2768 |   9049.44 | INDONESIA      | ZtmMQwHl3OEEmRMFIeRTDuQhEuDVNaQIl31a     | 19-680-834-9042 | he slyly bold accounts. carefully ironic foxes cajole bravely                                           |
|     57454 | Customer#000057454 | 677202.9870 |   9740.34 | ETHIOPIA       | 3UuMxx1kC9yhoWNVszz7zfVN9817toLaS        | 15-589-139-5007 | e fluffily. express foxes among the slyly final in                                                      |
+-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+---------------------------------------------------------------------------------------------------------+
20 rows in set (1 min 46.38 sec)

〇〇

mysql> explain -- 11
    -> select
    ->  ps_partkey,
    ->  sum(ps_supplycost * ps_availqty) as value
    -> from
    ->  partsupp,
    ->  supplier,
    ->  nation
    -> where
    ->  ps_suppkey = s_suppkey
    ->  and s_nationkey = n_nationkey
    ->  and n_name = 'JAPAN'
    -> group by
    ->  ps_partkey having
    ->   sum(ps_supplycost * ps_availqty) > (
    ->    select
    ->     sum(ps_supplycost * ps_availqty) * 0.0001000000
    ->    from
    ->     partsupp,
    ->     supplier,
    ->     nation
    ->    where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'JAPAN'
    ->   )
    -> order by
    ->  value desc limit  10;
+----+-------------+----------+--------+----------------------+--------------+---------+---------------------------+-------+----------------------------------------------+
| id | select_type | table    | type   | possible_keys        | key          | key_len | ref                       | rows  | Extra                                        |
+----+-------------+----------+--------+----------------------+--------------+---------+---------------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | supplier | index  | PRIMARY,supplier_fk1 | supplier_fk1 | 4       | NULL                      | 99766 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY     | nation   | eq_ref | PRIMARY              | PRIMARY      | 4       | tpch.supplier.s_nationkey |     1 | Using where                                  |
|  1 | PRIMARY     | partsupp | ref    | partsupp_fk1         | partsupp_fk1 | 4       | tpch.supplier.s_suppkey   |    42 |                                              |
|  2 | SUBQUERY    | supplier | index  | PRIMARY,supplier_fk1 | supplier_fk1 | 4       | NULL                      | 99766 | Using index                                  |
|  2 | SUBQUERY    | nation   | eq_ref | PRIMARY              | PRIMARY      | 4       | tpch.supplier.s_nationkey |     1 | Using where                                  |
|  2 | SUBQUERY    | partsupp | ref    | partsupp_fk1         | partsupp_fk1 | 4       | tpch.supplier.s_suppkey   |    42 |                                              |
+----+-------------+----------+--------+----------------------+--------------+---------+---------------------------+-------+----------------------------------------------+
6 rows in set (0.00 sec)

mysql> -- 11
mysql> select
    ->  ps_partkey,
    ->  sum(ps_supplycost * ps_availqty) as value
    -> from
    ->  partsupp,
    ->  supplier,
    ->  nation
    -> where
    ->  ps_suppkey = s_suppkey
    ->  and s_nationkey = n_nationkey
    ->  and n_name = 'JAPAN'
    -> group by
    ->  ps_partkey having
    ->   sum(ps_supplycost * ps_availqty) > (
    ->    select
    ->     sum(ps_supplycost * ps_availqty) * 0.0001000000
    ->    from
    ->     partsupp,
    ->     supplier,
    ->     nation
    ->    where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'JAPAN'
    ->   )
    -> order by
    ->  value desc limit  10;
Empty set (9.33 sec)

mysql>

〇〇

mysql> explain -- 12
    -> select
    ->  l_shipmode,
    ->  sum(case
    ->   when o_orderpriority = '1-URGENT'
    ->    or o_orderpriority = '2-HIGH'
    ->    then 1
    ->   else 0
    ->  end) as high_line_count,
    ->  sum(case
    ->   when o_orderpriority <> '1-URGENT'
    ->    and o_orderpriority <> '2-HIGH'
    ->    then 1
    ->   else 0
    ->  end) as low_line_count
    -> from
    ->  orders,
    ->  lineitem
    -> where
    ->  o_orderkey = l_orderkey
    ->  and l_shipmode in ('FOB', 'MAIL')
    ->  and l_commitdate < l_receiptdate
    ->  and l_shipdate < l_commitdate
    ->  and l_receiptdate >= date '1993-01-01'
    ->  and l_receiptdate < date '1993-01-01' + interval '1' year
    -> group by
    ->  l_shipmode
    -> order by
    ->  l_shipmode limit  10;
+----+-------------+----------+------+---------------+---------+---------+------------------------+----------+---------------------------------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref                    | rows     | Extra                           |
+----+-------------+----------+------+---------------+---------+---------+------------------------+----------+---------------------------------+
|  1 | SIMPLE      | orders   | ALL  | PRIMARY       | NULL    | NULL    | NULL                   | 15016933 | Using temporary; Using filesort |
|  1 | SIMPLE      | lineitem | ref  | PRIMARY       | PRIMARY | 4       | tpch.orders.o_orderkey |        2 | Using where                     |
+----+-------------+----------+------+---------------+---------+---------+------------------------+----------+---------------------------------+
2 rows in set (0.00 sec)

mysql> -- 12
mysql> select
    ->  l_shipmode,
    ->  sum(case
    ->   when o_orderpriority = '1-URGENT'
    ->    or o_orderpriority = '2-HIGH'
    ->    then 1
    ->   else 0
    ->  end) as high_line_count,
    ->  sum(case
    ->   when o_orderpriority <> '1-URGENT'
    ->    and o_orderpriority <> '2-HIGH'
    ->    then 1
    ->   else 0
    ->  end) as low_line_count
    -> from
    ->  orders,
    ->  lineitem
    -> where
    ->  o_orderkey = l_orderkey
    ->  and l_shipmode in ('FOB', 'MAIL')
    ->  and l_commitdate < l_receiptdate
    ->  and l_shipdate < l_commitdate
    ->  and l_receiptdate >= date '1993-01-01'
    ->  and l_receiptdate < date '1993-01-01' + interval '1' year
    -> group by
    ->  l_shipmode
    -> order by
    ->  l_shipmode limit  10;
+------------+-----------------+----------------+
| l_shipmode | high_line_count | low_line_count |
+------------+-----------------+----------------+
| FOB        |           62738 |          93486 |
| MAIL       |           62206 |          93979 |
+------------+-----------------+----------------+
2 rows in set (1 min 21.24 sec)

〇〇

mysql> explain -- 13
    -> 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 limit  10;
+----+-------------+------------+-------+---------------+------------+---------+-------------------------+---------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref                     | rows    | Extra                           |
+----+-------------+------------+-------+---------------+------------+---------+-------------------------+---------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL       | NULL    | NULL                    | 1500000 | Using temporary; Using filesort |
|  2 | DERIVED     | customer   | index | NULL          | PRIMARY    | 4       | NULL                    |  212962 | Using index                     |
|  2 | DERIVED     | orders     | ref   | orders_fk1    | orders_fk1 | 4       | tpch.customer.c_custkey |       7 |                                 |
+----+-------------+------------+-------+---------------+------------+---------+-------------------------+---------+---------------------------------+
3 rows in set (1 min 0.12 sec)

mysql> -- 13
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 limit  10;
+---------+----------+
| 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 3.38 sec)

mysql>

〇〇

mysql> explain
    ->  -- 14
    -> select
    ->  100.00 * sum(case
    ->   when p_type like 'PROMO%'
    ->    then l_extendedprice * (1 - l_discount)
    ->   else 0
    ->  end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
    -> from
    ->  lineitem,
    ->  part
    -> where
    ->  l_partkey = p_partkey
    ->  and l_shipdate >= date '1993-07-01'
    ->  and l_shipdate < date '1993-07-01' + interval '1' month limit  10;
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+----------+-------------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows     | Extra       |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+----------+-------------+
|  1 | SIMPLE      | lineitem | ALL    | NULL          | NULL    | NULL    | NULL                    | 60070444 | Using where |
|  1 | SIMPLE      | part     | eq_ref | PRIMARY       | PRIMARY | 4       | tpch.lineitem.l_partkey |        1 |             |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+----------+-------------+
2 rows in set (0.01 sec)

mysql>
mysql>  -- 14
mysql> select
    ->  100.00 * sum(case
    ->   when p_type like 'PROMO%'
    ->    then l_extendedprice * (1 - l_discount)
    ->   else 0
    ->  end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
    -> from
    ->  lineitem,
    ->  part
    -> where
    ->  l_partkey = p_partkey
    ->  and l_shipdate >= date '1993-07-01'
    ->  and l_shipdate < date '1993-07-01' + interval '1' month limit  10;
+---------------+
| promo_revenue |
+---------------+
| 16.6088216759 |
+---------------+
1 row in set (38.53 sec)

〇〇

mysql> explain -- 15
    -> select
    ->  s_suppkey,
    ->  s_name,
    ->  s_address,
    ->  s_phone,
    ->  total_revenue
    -> from
    ->  supplier,
    ->   (
    ->  select
    ->   l_suppkey supplier_no,
    ->   sum(l_extendedprice * (1 - l_discount))total_revenue
    ->  from
    ->   lineitem
    ->  where
    ->   l_shipdate >= date '1995-02-01'
    ->   and l_shipdate < date '1995-02-01' + interval '3' month
    ->  group by
    ->   l_suppkey
    ->  )
    ->  revenue0
    -> where
    ->  s_suppkey = supplier_no
    ->  and total_revenue = (
    ->   select
    ->    max(total_revenue)
    ->   from
    ->   (
    ->  select
    ->   l_suppkey supplier_no,
    ->   sum(l_extendedprice * (1 - l_discount))total_revenue
    ->  from
    ->   lineitem
    ->  where
    ->   l_shipdate >= date '1995-02-01'
    ->   and l_shipdate < date '1995-02-01' + interval '3' month
    ->  group by
    ->   l_suppkey
    ->  )  revenue0
    ->  )
    -> order by
    ->  s_suppkey limit  10;
+----+-------------+------------+--------+---------------+---------+---------+----------------------+----------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref                  | rows     | Extra                                        |
+----+-------------+------------+--------+---------------+---------+---------+----------------------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL                 |   100000 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | supplier   | eq_ref | PRIMARY       | PRIMARY | 4       | revenue0.supplier_no |        1 |                                              |
|  3 | SUBQUERY    | <derived4> | ALL    | NULL          | NULL    | NULL    | NULL                 |   100000 |                                              |
|  4 | DERIVED     | lineitem   | ALL    | NULL          | NULL    | NULL    | NULL                 | 60070444 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | lineitem   | ALL    | NULL          | NULL    | NULL    | NULL                 | 60070444 | Using where; Using temporary; Using filesort |
+----+-------------+------------+--------+---------------+---------+---------+----------------------+----------+----------------------------------------------+
5 rows in set (1 min 23.26 sec)

mysql> -- 15
mysql> select
    ->  s_suppkey,
    ->  s_name,
    ->  s_address,
    ->  s_phone,
    ->  total_revenue
    -> from
    ->  supplier,
    ->   (
    ->  select
    ->   l_suppkey supplier_no,
    ->   sum(l_extendedprice * (1 - l_discount))total_revenue
    ->  from
    ->   lineitem
    ->  where
    ->   l_shipdate >= date '1995-02-01'
    ->   and l_shipdate < date '1995-02-01' + interval '3' month
    ->  group by
    ->   l_suppkey
    ->  )
    ->  revenue0
    -> where
    ->  s_suppkey = supplier_no
    ->  and total_revenue = (
    ->   select
    ->    max(total_revenue)
    ->   from
    ->   (
    ->  select
    ->   l_suppkey supplier_no,
    ->   sum(l_extendedprice * (1 - l_discount))total_revenue
    ->  from
    ->   lineitem
    ->  where
    ->   l_shipdate >= date '1995-02-01'
    ->   and l_shipdate < date '1995-02-01' + interval '3' month
    ->  group by
    ->   l_suppkey
    ->  )  revenue0
    ->  )
    -> order by
    ->  s_suppkey limit  10;
+-----------+--------------------+---------------------+-----------------+---------------+
| s_suppkey | s_name             | s_address           | s_phone         | total_revenue |
+-----------+--------------------+---------------------+-----------------+---------------+
|     83966 | Supplier#000083966 | 0ITp9HCIUHEHgWCjeTt | 24-897-113-5492 |  2147201.6871 |
+-----------+--------------------+---------------------+-----------------+---------------+
1 row in set (1 min 24.81 sec)

mysql>

〇〇

mysql> explain -- 16
    -> select
    ->  p_brand,
    ->  p_type,
    ->  p_size,
    ->  count(distinct ps_suppkey) as supplier_cnt
    -> from
    ->  partsupp,
    ->  part
    -> where
    ->  p_partkey = ps_partkey
    ->  and p_brand <> 'Brand#12'
    ->  and p_type not like 'MEDIUM BRUSHED%'
    ->  and p_size in (28, 5, 17, 10, 41, 42, 15, 27)
    ->  and ps_suppkey not in (
    ->   select
    ->    s_suppkey
    ->   from
    ->    supplier
    ->   where
    ->    s_comment like '%Customer%Complaints%'
    ->  )
    -> group by
    ->  p_brand,
    ->  p_type,
    ->  p_size
    -> order by
    ->  supplier_cnt desc,
    ->  p_brand,
    ->  p_type,
    ->  p_size limit  10;
+----+--------------------+----------+-----------------+---------------+---------+---------+---------------------+---------+----------------------------------------------+
| id | select_type        | table    | type            | possible_keys | key     | key_len | ref                 | rows    | Extra                                        |
+----+--------------------+----------+-----------------+---------------+---------+---------+---------------------+---------+----------------------------------------------+
|  1 | PRIMARY            | part     | ALL             | PRIMARY       | NULL    | NULL    | NULL                | 1997259 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | partsupp | ref             | PRIMARY       | PRIMARY | 4       | tpch.part.p_partkey |       2 | Using where; Using index                     |
|  2 | DEPENDENT SUBQUERY | supplier | unique_subquery | PRIMARY       | PRIMARY | 4       | func                |       1 | Using where                                  |
+----+--------------------+----------+-----------------+---------------+---------+---------+---------------------+---------+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> -- 16
mysql> select
    ->  p_brand,
    ->  p_type,
    ->  p_size,
    ->  count(distinct ps_suppkey) as supplier_cnt
    -> from
    ->  partsupp,
    ->  part
    -> where
    ->  p_partkey = ps_partkey
    ->  and p_brand <> 'Brand#12'
    ->  and p_type not like 'MEDIUM BRUSHED%'
    ->  and p_size in (28, 5, 17, 10, 41, 42, 15, 27)
    ->  and ps_suppkey not in (
    ->   select
    ->    s_suppkey
    ->   from
    ->    supplier
    ->   where
    ->    s_comment like '%Customer%Complaints%'
    ->  )
    -> group by
    ->  p_brand,
    ->  p_type,
    ->  p_size
    -> order by
    ->  supplier_cnt desc,
    ->  p_brand,
    ->  p_type,
    ->  p_size limit  10;
+----------+---------------------------+--------+--------------+
| p_brand  | p_type                    | p_size | supplier_cnt |
+----------+---------------------------+--------+--------------+
| Brand#33 | STANDARD BURNISHED COPPER |     42 |          116 |
| Brand#51 | PROMO PLATED STEEL        |     28 |          104 |
| Brand#11 | ECONOMY ANODIZED COPPER   |     28 |          100 |
| Brand#42 | PROMO BURNISHED NICKEL    |     27 |          100 |
| Brand#11 | ECONOMY POLISHED COPPER   |     41 |           99 |
| Brand#51 | ECONOMY PLATED NICKEL     |     10 |           96 |
| Brand#14 | ECONOMY PLATED COPPER     |     42 |           95 |
| Brand#43 | MEDIUM PLATED NICKEL      |     28 |           95 |
| Brand#11 | LARGE POLISHED COPPER     |     10 |           92 |
| Brand#15 | ECONOMY BURNISHED NICKEL  |     28 |           92 |
+----------+---------------------------+--------+--------------+
10 rows in set (10.86 sec)

mysql>