mysql> source /home/ushastry/mybuilds/mysql-5.6.11-release/ssb_schema_partitioned.sql;

mysql> LOAD DATA INFILE '/tmp/ssb/lineorder_1995_12.tbl' INTO TABLE lineorder fields terminated by '\t';
Query OK, 747011 rows affected (1 min 48.94 sec)
Records: 747011  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/ssb/customer.tbl' INTO TABLE customer fields terminated by '\t';
Query OK, 300000 rows affected (2 min 34.49 sec)
Records: 300000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/ssb/part.tbl' INTO TABLE part fields terminated by '\t';
Query OK, 800000 rows affected (6 min 1.75 sec)
Records: 800000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/ssb/supplier.tbl' INTO TABLE supplier fields terminated by '\t';
Query OK, 20000 rows affected (9.19 sec)
Records: 20000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/ssb/dim_date.tbl' INTO TABLE dim_date fields terminated by '\t';
Query OK, 2556 rows affected (1.19 sec)
Records: 2556  Deleted: 0  Skipped: 0  Warnings: 0

mysql> alter table lineorder
    -> add key(lo_orderdatekey),
    -> add key(lo_suppkey),
    -> add key(lo_partkey),
    -> add key(lo_custkey);
Query OK, 0 rows affected (4 min 18.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE lineorder stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customer stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE supplier stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE part stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE dim_date stats_persistent=1, stats_auto_recalc=0;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table lineorder;
1 row in set (1.86 sec)

mysql> analyze table customer;
1 row in set (0.69 sec)

mysql> analyze table supplier;
1 row in set (0.12 sec)

mysql> analyze table part;
1 row in set (0.22 sec)

mysql> analyze table dim_date;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.dim_date | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.12 sec)

mysql> 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      | part      | NULL       | range  | PRIMARY,P_Brand                       | P_Brand    | 33      | NULL                           | 11164 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_PartKey | 3       | test.part.P_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.13 sec)

mysql> select count(*) From information_Schema.partitions;
+----------+
| count(*) |
+----------+
|      227 |
+----------+
1 row in set (0.12 sec)

mysql> 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      | dim_date  | NULL       | range  | PRIMARY                               | PRIMARY         | 4       | NULL                      |   28 | Using where; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | test.dim_date.D_DateKey   |    1 | NULL                         |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 3       | test.lineorder.LO_PartKey |    1 | Using where                  |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 2       | test.lineorder.LO_SuppKey |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+---------------------------+------+------------------------------+
4 rows in set (0.00 sec)

mysql> 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      | dim_date  | NULL       | range  | PRIMARY                               | PRIMARY         | 4       | NULL                      |   28 | Using where; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_OrderDateKey | 4       | test.dim_date.D_DateKey   |    1 | NULL                         |
|  1 | SIMPLE      | part      | NULL       | eq_ref | PRIMARY,P_Brand                       | PRIMARY         | 3       | test.lineorder.LO_PartKey |    1 | Using where                  |
|  1 | SIMPLE      | supplier  | NULL       | eq_ref | PRIMARY                               | PRIMARY         | 2       | test.lineorder.LO_SuppKey |    1 | Using where                  |
+----+-------------+-----------+------------+--------+---------------------------------------+-----------------+---------+---------------------------+------+------------------------------+
4 rows in set (0.01 sec)

mysql> select count(*) From information_Schema.partitions;                                                                                                                       +----------+
| count(*) |
+----------+
|      227 |
+----------+
1 row in set (0.04 sec)

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

mysql> 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      | part      | NULL       | range  | PRIMARY,P_Brand                       | P_Brand    | 33      | NULL                           | 11164 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | lineorder | p1995_12   | ref    | LO_OrderDateKey,LO_SuppKey,LO_PartKey | LO_PartKey | 3       | test.part.P_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.11 sec)

mysql>