mysql 5.5 tpc-h测试

〇〇 · 发表于 2011-6-5 17:17

〇〇 · 发表于 2011-6-5 17:31

〇〇 · 发表于 2011-6-9 15:26

mysql> explain select
->  c_name,
->  c_custkey,
->  o_orderkey,
->  o_orderdate,
->  o_totalprice,
->  sum(l_quantity)
-> from
->  customer,
->  orders,
->  lineitem l,
-> (
-> select
-> l_orderkey
-> from
-> lineitem
-> group by
-> l_orderkey having
->    sum(l_quantity) > 313
->  )l1
-> where
->  o_orderkey =l1.l_orderkey
->  and c_custkey = o_custkey
->  and o_orderkey = l.l_orderkey
-> group by
->  c_name,
->  c_custkey,
->  o_orderkey,
->  o_orderdate,
->  o_totalprice
-> order by
->  o_totalprice desc,
->  o_orderdate limit  10;

+----+-------------+------------+--------+--------------------+---------+---------+------------------------+----------+---------------------------------+
| id | select_type | table    | type | possible_keys    | key    | key_len | ref                   | rows    | Extra                         |
+----+-------------+------------+--------+--------------------+---------+---------+------------------------+----------+---------------------------------+
|  1 | PRIMARY    | <derived2> | ALL | NULL             | NULL | NULL | NULL                |    99 | Using temporary; Using filesort |
|  1 | PRIMARY    | orders    | eq_ref | PRIMARY,orders_fk1 | PRIMARY | 4    | l1.l_orderkey       |       1 |                               |
|  1 | PRIMARY    | customer | eq_ref | PRIMARY          | PRIMARY | 4    | tpch.orders.o_custkey  |       1 |                               |
|  1 | PRIMARY    | l       | ref | PRIMARY          | PRIMARY | 4    | tpch.orders.o_orderkey |       1 | Using where                   |
|  2 | DERIVED    | lineitem | index  | NULL             | PRIMARY | 8    | NULL                | 60272249 |                               |
+----+-------------+------------+--------+--------------------+---------+---------+------------------------+----------+---------------------------------+
5 rows in set (33.18 sec)

mysql>
mysql> select
->  c_name,
->  c_custkey,
->  o_orderkey,
->  o_orderdate,
->  o_totalprice,
->  sum(l_quantity)
-> from
->  customer,
->  orders,
->  lineitem l,
-> (
-> select
-> l_orderkey
-> from
-> lineitem
-> group by
-> l_orderkey having
->    sum(l_quantity) > 313
->  )l1
-> where
->  o_orderkey =l1.l_orderkey
->  and c_custkey = o_custkey
->  and o_orderkey = l.l_orderkey
-> group by
->  c_name,
->  c_custkey,
->  o_orderkey,
->  o_orderdate,
->  o_totalprice
-> order by
->  o_totalprice desc,
->  o_orderdate limit  10;
+--------------------+-----------+------------+-------------+--------------+-----------------+
| c_name          | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum(l_quantity) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#001287812 | 1287812 | 42290181 | 1997-11-26  | 558289.17 |       318.00 |
| Customer#001172513 | 1172513 | 36667107 | 1997-06-06  | 550142.18 |       322.00 |
| Customer#000571654 | 571654 | 21213895 | 1992-01-03  | 549380.08 |       327.00 |
| Customer#000667882 | 667882 | 2199712 | 1996-09-30  | 542154.01 |       327.00 |
| Customer#001471966 | 1471966 | 1263015 | 1997-02-02  | 540476.80 |       320.00 |
| Customer#001101754 | 1101754 | 46794464 | 1992-04-28  | 532623.90 |       320.00 |
| Customer#000591466 | 591466 | 55799200 | 1996-02-11  | 524209.45 |       327.00 |
| Customer#001196317 | 1196317 | 34509573 | 1995-10-15  | 522897.01 |       318.00 |
| Customer#000634048 | 634048 | 36327201 | 1992-04-25  | 519634.30 |       315.00 |
| Customer#001308376 | 1308376 | 28077922 | 1996-10-27  | 518711.99 |       319.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
10 rows in set (33.21 sec)

mysql>

〇〇 · 发表于 2011-6-10 15:53

改用innodb_file_per_table

mysql> create table tpch2.lineitem as select * from lineitem;
Query OK, 59986052 rows affected (13 min 29.94 sec)
Records: 59986052  Duplicates: 0  Warnings: 0

mysql> create table tpch2.partsupp as select * from partsupp;
Query OK, 8000000 rows affected (1 min 37.88 sec)
Records: 8000000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.part as select * from part;
Query OK, 2000000 rows affected (20.01 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.orders as select * from orders;
Query OK, 15000000 rows affected (2 min 55.07 sec)
Records: 15000000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.customers as select * from customers;
ERROR 1146 (42S02): Table 'tpch.customers' doesn't exist
mysql> create table tpch2.customer as select * from customer;
Query OK, 1500000 rows affected (24.22 sec)
Records: 1500000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.supplier as select * from supplier;
Query OK, 100000 rows affected (2.58 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.nation as select * from nation;
Query OK, 25 rows affected (0.05 sec)
Records: 25  Duplicates: 0  Warnings: 0

mysql> create table tpch2.region as select * from region;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

〇〇 · 发表于 2011-6-10 16:15

创建主键，占用空间反而减少？
前
-rw-rw---- 1 mysql mysql    8874 06-10 15:37 part.frm
-rw-rw---- 1 mysql mysql  348127232 06-10 15:38 part.ibd

后

-rw-rw---- 1 mysql mysql    8874 06-10 16:11 part.frm
-rw-rw---- 1 mysql mysql  335544320 06-10 16:12 part.ibd

〇〇 · 发表于 2011-6-11 22:09

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 7.73 sec)

mysql> -- 22
mysql> select
->  cntrycode,
->  count(*) as numcust,
->  sum(c_acctbal) as totacctbal
-> from
->  (
-> select
-> substr(c_phone ,1 ,2) as cntrycode,
-> c_acctbal
-> from
-> customer
-> where
-> substr(c_phone ,1 ,2) in
->    ('23', '39', '40', '27', '33', '31', '25')
-> and c_acctbal > (
->    select
->    avg(c_acctbal)
->    from
->    customer
->    where
->    c_acctbal > 0.00
->    and substr(c_phone ,1 ,2) in
->    ('23', '39', '40', '27', '33', '31', '25')
-> )
-> and not exists (
->    select
->    *
->    from
->    orders
->    where
->    o_custkey = c_custkey
-> )
->  ) custsale
-> group by
->  cntrycode
-> order by
->  cntrycode limit  10;
+-----------+---------+-------------+
| cntrycode | numcust | totacctbal  |
+-----------+---------+-------------+
| 23       | 8988 | 67627762.48 |
| 25       | 9111 | 68360817.05 |
| 27       | 9014 | 67640913.65 |
| 31       | 9087 | 68149522.82 |
| 33       | 9182 | 68895118.67 |
+-----------+---------+-------------+
5 rows in set (3.04 sec)

mysql> -- 21
mysql> select
->  s_name,
->  count(*) as numwait
-> from
->  supplier,
->  lineitem l1,
->  orders,
->  nation
-> where
->  s_suppkey = l1.l_suppkey
->  and o_orderkey = l1.l_orderkey
->  and o_orderstatus = 'F'
->  and l1.l_receiptdate > l1.l_commitdate
->  and exists (
-> select
-> *
-> from
-> lineitem l2
-> where
-> l2.l_orderkey = l1.l_orderkey
-> and l2.l_suppkey <> l1.l_suppkey
->  )
->  and not exists (
-> select
-> *
-> from
-> lineitem l3
-> where
-> l3.l_orderkey = l1.l_orderkey
-> and l3.l_suppkey <> l1.l_suppkey
-> and l3.l_receiptdate > l3.l_commitdate
->  )
->  and s_nationkey = n_nationkey
->  and n_name = 'GERMANY'
-> group by
->  s_name
-> order by
->  numwait desc,
->  s_name limit  10;
+--------------------+---------+
| s_name          | numwait |
+--------------------+---------+
| Supplier#000007962 |    22 |
| Supplier#000033421 |    22 |
| Supplier#000018276 |    21 |
| Supplier#000028758 |    21 |
| Supplier#000090945 |    21 |
| Supplier#000014045 |    20 |
| Supplier#000014173 |    20 |
| Supplier#000017352 |    20 |
| Supplier#000025900 |    20 |
| Supplier#000032900 |    20 |
+--------------------+---------+
10 rows in set (3 min 21.40 sec)

mysql> -- 19
select
mysql> select
->  sum(l_extendedprice* (1 - l_discount)) as revenue
-> from
->  lineitem,
->  part
-> where
->  (
-> p_partkey = l_partkey
-> and p_brand = 'Brand#31'
-> and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
-> and l_quantity >= 3 and l_quantity <= 3 + 10
-> and p_size between 1 and 5
-> and l_shipmode in ('AIR', 'AIR REG')
-> and l_shipinstruct = 'DELIVER IN PERSON'
->  )
->  or
->  (
-> p_partkey = l_partkey
-> and p_brand = 'Brand#21'
-> and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
-> and l_quantity >= 15 and l_quantity <= 15 + 10
-> and p_size between 1 and 10
-> and l_shipmode in ('AIR', 'AIR REG')
-> and l_shipinstruct = 'DELIVER IN PERSON'
->  )
->  or
->  (
-> p_partkey = l_partkey
-> and p_brand = 'Brand#45'
-> and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
-> and l_quantity >= 23 and l_quantity <= 23 + 10
-> and p_size between 1 and 15
-> and l_shipmode in ('AIR', 'AIR REG')
-> and l_shipinstruct = 'DELIVER IN PERSON'
->  ) limit  10;
+---------------+
| revenue    |
+---------------+
| 35382266.2072 |
+---------------+
1 row in set (59.92 sec)

mysql> -- 18 c
mysql> select
->  c_name,
->  c_custkey,
->  o_orderkey,
->  o_orderdate,
->  o_totalprice,
->  sum(l_quantity)
-> from
->  customer,
->  orders,
->  lineitem l,
-> (
-> select
-> l_orderkey
-> from
-> lineitem
-> group by
-> l_orderkey having
->    sum(l_quantity) > 313
->  )l1
-> where
->  o_orderkey =l1.l_orderkey
->  and c_custkey = o_custkey
->  and o_orderkey = l.l_orderkey
-> group by
->  c_name,
->  c_custkey,
->  o_orderkey,
->  o_orderdate,
->  o_totalprice
-> order by
->  o_totalprice desc,
->  o_orderdate limit  10;

+--------------------+-----------+------------+-------------+--------------+-----------------+
| c_name          | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum(l_quantity) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#001287812 | 1287812 | 42290181 | 1997-11-26  | 558289.17 |       318.00 |
| Customer#001172513 | 1172513 | 36667107 | 1997-06-06  | 550142.18 |       322.00 |
| Customer#000571654 | 571654 | 21213895 | 1992-01-03  | 549380.08 |       327.00 |
| Customer#000667882 | 667882 | 2199712 | 1996-09-30  | 542154.01 |       327.00 |
| Customer#001471966 | 1471966 | 1263015 | 1997-02-02  | 540476.80 |       320.00 |
| Customer#001101754 | 1101754 | 46794464 | 1992-04-28  | 532623.90 |       320.00 |
| Customer#000591466 | 591466 | 55799200 | 1996-02-11  | 524209.45 |       327.00 |
| Customer#001196317 | 1196317 | 34509573 | 1995-10-15  | 522897.01 |       318.00 |
| Customer#000634048 | 634048 | 36327201 | 1992-04-25  | 519634.30 |       315.00 |
| Customer#001308376 | 1308376 | 28077922 | 1996-10-27  | 518711.99 |       319.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
10 rows in set (33.39 sec)

mysql>
mysql> -- 21 fix
mysql> select s_name ,count(*) as numwait from
-> (select
->  s_name,l_suppkey ,l_orderkey
->  -- count(*) as numwait
-> from
->  supplier,
->  lineitem l1,
->  orders,
->  nation
-> where
->  s_suppkey = l1.l_suppkey
->  and o_orderkey = l1.l_orderkey
->  and o_orderstatus = 'F'
->  and l1.l_receiptdate > l1.l_commitdate
->  and s_nationkey = n_nationkey
->  and n_name = 'GERMANY'
-> ) l1
-> where exists (
->  select
->  *
->  from
->  lineitem l2
->  where
->  l2.l_orderkey = l1.l_orderkey
->  and l2.l_suppkey <> l1.l_suppkey
->  )
->  and not exists (
->  select
->  *
->  from
->  lineitem l3
->  where
->  l3.l_orderkey = l1.l_orderkey
->  and l3.l_suppkey <> l1.l_suppkey
->  and l3.l_receiptdate > l3.l_commitdate
->  )
-> group by
->  s_name
-> order by
->  numwait desc,
->  s_name
-> limit 10;
+--------------------+---------+
| s_name          | numwait |
+--------------------+---------+
| Supplier#000007962 |    22 |
| Supplier#000033421 |    22 |
| Supplier#000018276 |    21 |
| Supplier#000028758 |    21 |
| Supplier#000090945 |    21 |
| Supplier#000014045 |    20 |
| Supplier#000014173 |    20 |
| Supplier#000017352 |    20 |
| Supplier#000025900 |    20 |
| Supplier#000032900 |    20 |
+--------------------+---------+
10 rows in set (1 min 44.05 sec)

mysql> -- 20 a
mysql> select s_name, s_address
-> from supplier, nation
-> where s_suppkey in (select ps_suppkey
->  from partsupp
->  ,(select l_partkey,l_suppkey, sum(l_quantity) l_quantity_SUM
->  from lineitem,part
->  where l_partkey = p_partkey and p_name like 'bisque%'
->  and l_shipdate >= date '1994-01-01'
->  and l_shipdate < date '1994-01-01' + interval '1' year
->  GROUP BY l_partkey,l_suppkey
->  )a
->  where l_partkey = ps_partkey
->  and l_suppkey = ps_suppkey
->  and ps_availqty > 0.5*l_quantity_SUM
->  )
->  and s_nationkey = n_nationkey
->  and n_name = 'CANADA'
-> order by s_name
-> limit 10;

〇〇 · 发表于 2011-6-11 22:11

〇〇 · 发表于 2011-6-12 05:24

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> source /root/qall_myf.sql
PROMPT set to '1 '
+--------------+--------------+--------------+------------------+--------------------+----------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty    | sum_base_price | sum_disc_price    | sum_charge          | avg_qty | avg_price | avg_disc | count_order |
+--------------+--------------+--------------+------------------+--------------------+----------------------+-----------+--------------+----------+-------------+
| A          | F          | 377518399.00 |  566065727797.25 |  537759104278.0656 |  559276670892.116819 | 25.500975 | 38237.151009 | 0.050007 | 14804077 |
| N          | F          | 9851614.00 | 14767438399.17 | 14028805792.2114 | 14590490998.366737 | 25.522448 | 38257.810660 | 0.049973 |    385998 |
| N          | O          | 750440706.00 | 1125264325266.58 | 1068995512488.7987 | 1111768538120.324852 | 25.498259 | 38233.907986 | 0.050000 | 29431057 |
| R          | F          | 377732830.00 |  566431054976.00 |  538110922664.7677 |  559634780885.086257 | 25.508385 | 38251.219274 | 0.049997 | 14808183 |
+--------------+--------------+--------------+------------------+--------------------+----------------------+-----------+--------------+----------+-------------+
4 rows in set (4 min 9.71 sec)

PROMPT set to '2 '
+-----------+--------------------+---------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| s_acctbal | s_name          | n_name       | p_partkey | p_mfgr       | s_address                               | s_phone       | s_comment                                                                                        |
+-----------+--------------------+---------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| 9997.03 | Supplier#000021474 | UNITED STATES | 746452 | Manufacturer#3 | B1WCuHtuY5Lm4bj,S                      | 34-502-253-8876 | posits are. regular braids about th                                                                |
| 9996.13 | Supplier#000048428 | CANADA       | 873403 | Manufacturer#2 | Z,m5TgvRq7oG4rsmvnE9Bn nemmJff54FkFAgk | 13-611-820-5223 | eodolites along the ideas de                                                                      |
| 9994.95 | Supplier#000036836 | BRAZIL       | 1436835 | Manufacturer#5 | HJF6WNqa,jTyW8LiX0dU2dmN8YmR tg       | 12-420-313-3017 | eful, special instructions. ironic, blithe theodolites nag quickly pending requests. quiet, even ac |
| 9994.83 | Supplier#000052301 | CANADA       | 202296 | Manufacturer#5 | tEE5cTg1Xv3tOKaAhfMfCDfybQFISdtt8V    | 13-437-784-3548 | re always instructions. ideas must have to detec                                                 |
| 9992.28 | Supplier#000060858 | ARGENTINA    | 1010837 | Manufacturer#3 | JY6ikmxTyvQlWD1nhvSygO6QptwWc9YG3vylw | 11-719-537-8037 | ithely idle deposits detect furiously about the even package                                     |
| 9990.67 | Supplier#000090925 | UNITED STATES | 1640892 | Manufacturer#1 | oFEr9GkQpXQw72 W4Y2DPLmQvIAGHPXMSF1H3p | 34-606-851-5651 | ly even requests. even courts sleep slyly busily final ideas. carefully sly theodolites          |
| 9989.90 | Supplier#000011269 | BRAZIL       | 686262 | Manufacturer#4 | V,4bksZDdE1Ux3YhH1UE0SeGZjudbbVvb,H3eA 3 | 12-917-310-3275 | egular accounts after the furiously express requests doubt above the slyly                         |
| 9989.46 | Supplier#000037658 | UNITED STATES | 462645 | Manufacturer#5 | SEh9v7C6WXZo855C5hu6sbkpRni72No       | 34-919-304-8360 | the instructions are furiously even requests.                                                    |
| 9988.34 | Supplier#000023569 | ARGENTINA    |    48568 | Manufacturer#3 | EZlxYBTNi,J1HLwEjPUhrlgnB IB2Ale       | 11-410-115-9353 | lly pending requests boost furiously. deposits across the fluffily unusual sentiments detect acr |
| 9987.04 | Supplier#000015601 | PERU       | 1890582 | Manufacturer#5 | 2UvlYtOSEoYOCYrgeWxjeIn7NLnx          | 27-993-727-8949 | ronic platelets. final asymptotes affix. silent packages ca                                        |
+-----------+--------------------+---------------+-----------+----------------+------------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
10 rows in set (2.50 sec)

PROMPT set to '3 '
+------------+-------------+-------------+----------------+
| l_orderkey | revenue    | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
| 21679558 | 451192.3341 | 1995-01-17  |             0 |
| 57978848 | 450250.4648 | 1995-02-19  |             0 |
| 24295076 | 437186.7777 | 1995-01-05  |             0 |
| 23679299 | 428116.4351 | 1995-02-28  |             0 |
|    492164 | 423270.2092 | 1995-02-19  |             0 |
| 35312039 | 422403.5320 | 1995-03-05  |             0 |
|    820962 | 418407.3101 | 1995-02-11  |             0 |
| 26731940 | 417950.7043 | 1995-02-27  |             0 |
| 36065633 | 417908.8665 | 1995-02-19  |             0 |
| 51262022 | 413274.3434 | 1995-02-11  |             0 |
+------------+-------------+-------------+----------------+
10 rows in set (28.01 sec)

PROMPT set to '4 '
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT       |    105597 |
| 2-HIGH       |    105754 |
| 3-MEDIUM       |    105026 |
| 4-NOT SPECIFIED |    104819 |
| 5-LOW          |    105309 |
+-----------------+-------------+
5 rows in set (13.17 sec)

PROMPT set to '5 '
+------------+----------------+
| n_name    | revenue       |
+------------+----------------+
| MOROCCO | 540038731.8977 |
| KENYA    | 536052927.7305 |
| ETHIOPIA | 520384942.7652 |
| MOZAMBIQUE | 520109603.6710 |
| ALGERIA | 519498315.2213 |
+------------+----------------+
5 rows in set (39.76 sec)

PROMPT set to '6 '
+-----------------+
| revenue       |
+-----------------+
| 2014944457.3560 |
+-----------------+
1 row in set (33.25 sec)

PROMPT set to '7 '
+----------------+----------------+--------+----------------+
| 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 (18.45 sec)

PROMPT set to '8 '
+--------+------------+
| o_year | mkt_share  |
+--------+------------+
| 1995 | 0.03780949 |
| 1996 | 0.03851161 |
+--------+------------+
2 rows in set (1 min 10.28 sec)

PROMPT set to '9'
+-----------+--------+----------------+
| nation | o_year | sum_profit    |
+-----------+--------+----------------+
| ALGERIA | 1998 | 352822350.0693 |
| ALGERIA | 1997 | 596671435.2478 |
| ALGERIA | 1996 | 602177243.0192 |
| ALGERIA | 1995 | 606831210.8801 |
| ALGERIA | 1994 | 595415097.2158 |
| ALGERIA | 1993 | 601347225.0045 |
| ALGERIA | 1992 | 594128993.7166 |
| ARGENTINA | 1998 | 366005512.6697 |
| ARGENTINA | 1997 | 614815785.9445 |
| ARGENTINA | 1996 | 621181784.9130 |
+-----------+--------+----------------+
10 rows in set (4 min 24.34 sec)

PROMPT set to '10 '
+-----------+--------------------+-------------+-----------+----------------+------------------------------------------+-----------------+---------------------------------------------------------------------------------------------------------+
| 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 45.03 sec)

PROMPT set to '11 '
Empty set (43.29 sec)

PROMPT set to '12 '
+------------+-----------------+----------------+
| l_shipmode | high_line_count | low_line_count |
+------------+-----------------+----------------+
| FOB       |          62738 |       93486 |
| MAIL    |          62206 |       93979 |
+------------+-----------------+----------------+
2 rows in set (1 min 22.84 sec)

PROMPT set to '13 '
+---------+----------+
| 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 16.67 sec)

PROMPT set to '14'
+---------------+
| promo_revenue |
+---------------+
| 16.6088216759 |
+---------------+
1 row in set (37.36 sec)

PROMPT set to '15 '
+-----------+--------------------+---------------------+-----------------+---------------+
| 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 21.39 sec)

PROMPT set to '16 '
+----------+---------------------------+--------+--------------+
| 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 (11.28 sec)

PROMPT set to '17 '
PROMPT set to '17c'
+----------------+
| avg_yearly    |
+----------------+
| 3188455.648571 |
+----------------+
1 row in set (6 min 0.24 sec)

PROMPT set to '18 '
PROMPT set to '18c'
+--------------------+-----------+------------+-------------+--------------+-----------------+
| c_name          | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum(l_quantity) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#001287812 | 1287812 | 42290181 | 1997-11-26  | 558289.17 |       318.00 |
| Customer#001172513 | 1172513 | 36667107 | 1997-06-06  | 550142.18 |       322.00 |
| Customer#000571654 | 571654 | 21213895 | 1992-01-03  | 549380.08 |       327.00 |
| Customer#000667882 | 667882 | 2199712 | 1996-09-30  | 542154.01 |       327.00 |
| Customer#001471966 | 1471966 | 1263015 | 1997-02-02  | 540476.80 |       320.00 |
| Customer#001101754 | 1101754 | 46794464 | 1992-04-28  | 532623.90 |       320.00 |
| Customer#000591466 | 591466 | 55799200 | 1996-02-11  | 524209.45 |       327.00 |
| Customer#001196317 | 1196317 | 34509573 | 1995-10-15  | 522897.01 |       318.00 |
| Customer#000634048 | 634048 | 36327201 | 1992-04-25  | 519634.30 |       315.00 |
| Customer#001308376 | 1308376 | 28077922 | 1996-10-27  | 518711.99 |       319.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
10 rows in set (33.28 sec)

PROMPT set to '19'
+---------------+
| revenue    |
+---------------+
| 35382266.2072 |
+---------------+
1 row in set (1 min 0.46 sec)

PROMPT set to '20 '
PROMPT set to '20a'

〇〇 · 发表于 2011-6-12 05:41

+--------------------+-----------------------------------------+
| s_name          | s_address                            |
+--------------------+-----------------------------------------+
| Supplier#000000020 | iybAE,RmTymrZVYaFZva2SH,j             |
| Supplier#000000091 | YV45D7TkfdQanOOZ7q9QxkyGUapU1oOWU6q3 |
| Supplier#000000205 | rF uV8d0JNEk                         |
| Supplier#000000285 | Br7e1nnt1yxrw6ImgpJ7YdhFDjuBf          |
| Supplier#000000287 | 7a9SP7qW5Yku5PvSg                      |
| Supplier#000000354 | w8fOo5W,aS                            |
| Supplier#000000361 | f8IUYRmdVXhQC9qJQjWknCXmzhe38vCbk6    |
| Supplier#000000475 | xw4V6,4QQW LI5Qg EOKy4JD B4Cq1tjzaOma9Y |
| Supplier#000000530 | 0qwCMwobKY OcmLyfRXlagA8ukENJv,       |
| Supplier#000000532 | ep92hT7VLaVlDKM7lgbj02kIL             |
+--------------------+-----------------------------------------+
10 rows in set (8 min 57.48 sec)

PROMPT set to '21 '
+--------------------+---------+
| s_name          | numwait |
+--------------------+---------+
| Supplier#000007962 |    22 |
| Supplier#000033421 |    22 |
| Supplier#000018276 |    21 |
| Supplier#000028758 |    21 |
| Supplier#000090945 |    21 |
| Supplier#000014045 |    20 |
| Supplier#000014173 |    20 |
| Supplier#000017352 |    20 |
| Supplier#000025900 |    20 |
| Supplier#000032900 |    20 |
+--------------------+---------+
10 rows in set (3 min 24.19 sec)

PROMPT set to '21 fix'
+--------------------+---------+
| s_name          | numwait |
+--------------------+---------+
| Supplier#000007962 |    22 |
| Supplier#000033421 |    22 |
| Supplier#000018276 |    21 |
| Supplier#000028758 |    21 |
| Supplier#000090945 |    21 |
| Supplier#000014045 |    20 |
| Supplier#000014173 |    20 |
| Supplier#000017352 |    20 |
| Supplier#000025900 |    20 |
| Supplier#000032900 |    20 |
+--------------------+---------+
10 rows in set (1 min 42.75 sec)

PROMPT set to '22 '
+-----------+---------+-------------+
| cntrycode | numcust | totacctbal  |
+-----------+---------+-------------+
| 23       | 8988 | 67627762.48 |
| 25       | 9111 | 68360817.05 |
| 27       | 9014 | 67640913.65 |
| 31       | 9087 | 68149522.82 |
| 33       | 9182 | 68895118.67 |
+-----------+---------+-------------+
5 rows in set (2.80 sec)

Returning to default PROMPT of mysql>
mysql>