(1)

// Version

mysql [localhost] {root} (test) > select version();
+-----------+
| version() |
+-----------+
| 5.6.11    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select version();
+-----------+
| version() |
+-----------+
| 5.6.11    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > show global variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)


// Create Schema

mysql [localhost] {root} (test) > source /data/ushastry/downloads/ssb/ssb_schema_partitioned.sql;
Query OK, 0 rows affected (0.27 sec)

Query OK, 0 rows affected (1.37 sec)

Query OK, 0 rows affected (0.17 sec)

Query OK, 0 rows affected (1.33 sec)

Query OK, 0 rows affected (0.20 sec)

Query OK, 0 rows affected (1.23 sec)

Query OK, 0 rows affected (0.17 sec)

Query OK, 0 rows affected (1.64 sec)

Query OK, 0 rows affected (13.58 sec)

Query OK, 0 rows affected (36.87 sec)


// Load data

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb//lineorder_1995_12.tbl' INTO TABLE lineorder fields terminated by '|';
Query OK, 747011 rows affected, 65535 warnings (19.14 sec)
Records: 747011  Deleted: 0  Skipped: 0  Warnings: 12699187

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/customer.tbl' INTO TABLE customer fields terminated by '|';
Query OK, 300000 rows affected, 65535 warnings (14.06 sec)
Records: 300000  Deleted: 0  Skipped: 0  Warnings: 2400000

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/part.tbl' INTO TABLE part fields terminated by '|';
Query OK, 800000 rows affected, 65535 warnings (29.13 sec)
Records: 800000  Deleted: 0  Skipped: 0  Warnings: 7200000

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/supplier.tbl' INTO TABLE supplier fields terminated by '|';
Query OK, 20000 rows affected, 65535 warnings (5.92 sec)
Records: 20000  Deleted: 0  Skipped: 0  Warnings: 140000

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/dim_date.tbl' INTO TABLE dim_date fields terminated by '|';
Query OK, 2556 rows affected, 43452 warnings (1.27 sec)
Records: 2556  Deleted: 0  Skipped: 0  Warnings: 43452



//Alter tables

mysql [localhost] {root} (test) > alter table lineorder
    -> add key(lo_orderdatekey),
    -> add key(lo_suppkey),
    -> add key(lo_partkey),
    -> add key(lo_custkey);
Query OK, 0 rows affected (7 min 9.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE lineorder stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE customer stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE part stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE part stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0;Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


// Analyze

mysql [localhost] {root} (test) > analyze table lineorder;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| test.lineorder | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (1 min 11.33 sec)

mysql [localhost] {root} (test) > analyze table customer;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.customer | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (1.00 sec)

mysql [localhost] {root} (test) > analyze table supplier;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.supplier | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.97 sec)

mysql [localhost] {root} (test) > analyze table part;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.part | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.64 sec)

mysql [localhost] {root} (test) > analyze table dim_date;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.dim_date | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.47 sec)


// explain

mysql [localhost] {root} (test) >
mysql [localhost] {root} (test) > explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey =
    ->  d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key             | key_len | ref                            | rows | Extra                        |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
|  1 | SIMPLE      | lineorder | p1995_12   | range  | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | NULL                           |    1 | Using where; Using temporary |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 3       | test.lineorder.LO_PartKey      |    1 | Using where                  |
|  1 | SIMPLE      | dim_date  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 4       | test.lineorder.LO_OrderDateKey |    1 | NULL                         |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 2       | test.lineorder.LO_SuppKey      |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
4 rows in set (0.01 sec)

mysql [localhost] {root} (test) > select count(*) from information_Schema.partitions;
+----------+
| count(*) |
+----------+
|      227 |
+----------+
1 row in set (0.01 sec)

mysql [localhost] {root} (test) > explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey =
    ->  d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key             | key_len | ref                            | rows | Extra                        |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
|  1 | SIMPLE      | lineorder | p1995_12   | range  | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | NULL                           |    1 | Using where; Using temporary |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 3       | test.lineorder.LO_PartKey      |    1 | Using where                  |
|  1 | SIMPLE      | dim_date  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 4       | test.lineorder.LO_OrderDateKey |    1 | NULL                         |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 2       | test.lineorder.LO_SuppKey      |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
4 rows in set (0.01 sec)

mysql [localhost] {root} (test) >


##################

(2)
mysql [localhost] {root} (test) > select version();
+-----------+
| version() |
+-----------+
| 5.6.11    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > show global variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > source /data/ushastry/downloads/ssb/ssb_schema_partitioned.sql;
Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.54 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.53 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.52 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.46 sec)

Query OK, 0 rows affected (3.60 sec)

Query OK, 0 rows affected (20.33 sec)

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb//lineorder_1995_12.tbl' INTO TABLE lineorder fields terminated by '|';
Query OK, 747011 rows affected, 65535 warnings (20.56 sec)
Records: 747011  Deleted: 0  Skipped: 0  Warnings: 12699187

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/customer.tbl' INTO TABLE customer fields terminated by '|';
Query OK, 300000 rows affected, 65535 warnings (14.49 sec)
Records: 300000  Deleted: 0  Skipped: 0  Warnings: 2400000

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/part.tbl' INTO TABLE part fields terminated by '|';
Query OK, 800000 rows affected, 65535 warnings (25.57 sec)
Records: 800000  Deleted: 0  Skipped: 0  Warnings: 7200000

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/supplier.tbl' INTO TABLE supplier fields terminated by '|';
Query OK, 20000 rows affected, 65535 warnings (8.89 sec)
Records: 20000  Deleted: 0  Skipped: 0  Warnings: 140000

mysql [localhost] {root} (test) > LOAD DATA INFILE '/data/ushastry/downloads/ssb/dim_date.tbl' INTO TABLE dim_date fields terminated by '|';
Query OK, 2556 rows affected, 43452 warnings (0.48 sec)
Records: 2556  Deleted: 0  Skipped: 0  Warnings: 43452

mysql [localhost] {root} (test) >


mysql [localhost] {root} (test) > alter table lineorder
    -> add key(lo_orderdatekey),
    -> add key(lo_suppkey),
    -> add key(lo_partkey),
    -> add key(lo_custkey);
Query OK, 0 rows affected (49.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE lineorder stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE customer stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE customer stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE part stats_persistent=1, stats_auto_recalc=0;
ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0;Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost] {root} (test) > ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql [localhost] {root} (test) > analyze table lineorder;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| test.lineorder | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql [localhost] {root} (test) > analyze table customer;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.customer | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql [localhost] {root} (test) > analyze table supplier;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.supplier | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > analyze table part;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.part | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.01 sec)

mysql [localhost] {root} (test) > analyze table dim_date;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.dim_date | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)



mysql [localhost] {root} (test) > explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey =
    ->  d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key             | key_len | ref                            | rows | Extra                        |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
|  1 | SIMPLE      | lineorder | p1995_12   | range  | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | NULL                           |    1 | Using where; Using temporary |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 3       | test.lineorder.LO_PartKey      |    1 | Using where                  |
|  1 | SIMPLE      | dim_date  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 4       | test.lineorder.LO_OrderDateKey |    1 | NULL                         |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 2       | test.lineorder.LO_SuppKey      |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
4 rows in set (0.00 sec)

mysql [localhost] {root} (test) >
mysql [localhost] {root} (test) > select count(*) from information_Schema.partitions;
+----------+
| count(*) |
+----------+
|      222 |
+----------+
1 row in set (0.01 sec)

mysql [localhost] {root} (test) >
mysql [localhost] {root} (test) > explain partitions SELECT SUM(lo_revenue) AS expr_841233792,d_year AS expr$0,p_brand AS expr$1 FROM lineorder AS `lineorder` JOIN dim_date AS `dim_date` ON( lo_orderdatekey =
    ->  d_datekey )  JOIN part AS `part` ON( lo_partkey = p_partkey )  JOIN supplier AS `supplier` ON( lo_suppkey = s_suppkey )  WHERE  p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' AND LO_OrderDateKey >= (19951131) AND  LO_OrderDateKey <  (19951231) GROUP BY expr$0,expr$1 ORDER BY NULL;
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                         | key             | key_len | ref                            | rows | Extra                        |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
|  1 | SIMPLE      | lineorder | p1995_12   | range  | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | NULL                           |    1 | Using where; Using temporary |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 3       | test.lineorder.LO_PartKey      |    1 | Using where                  |
|  1 | SIMPLE      | dim_date  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 4       | test.lineorder.LO_OrderDateKey |    1 | NULL                         |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 2       | test.lineorder.LO_SuppKey      |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+--------------------------------+------+------------------------------+
4 rows in set (0.00 sec)