wget https://github.com/greenlion/ssb-dbgen/archive/master.zip unzip master.zip cd ssb-dbgen-master/ make ./dbgen -T l SSBM (Star Schema Benchmark) Population Generator (Version 1.0.0) Copyright Transaction Processing Performance Council 1994 - 2000 ls -lhtr|grep lineorder.tbl -rw-r--r-- 1 umshastr common 572M Aug 10 14:36 lineorder.tbl -- rm -rf 100471/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/100471 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/100471 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/100471/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --performance-schema=ON 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock --loose-local-infile=1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.21 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> CREATE TABLE `lineorderi` ( -> `LO_OrderKey` bigint DEFAULT NULL, -> `LO_LineNumber` tinyint DEFAULT NULL, -> `LO_CustKey` int DEFAULT NULL, -> `LO_PartKey` int DEFAULT NULL, -> `LO_SuppKey` int DEFAULT NULL, -> `LO_OrderDateKey` int DEFAULT NULL, -> `LO_OrderPriority` varchar(15) DEFAULT NULL, -> `LO_ShipPriority` char(1) DEFAULT NULL, -> `LO_Quantity` tinyint DEFAULT NULL, -> `LO_ExtendedPrice` int DEFAULT NULL, -> `LO_OrdTotalPrice` int DEFAULT NULL, -> `LO_Discount` int DEFAULT NULL, -> `LO_Revenue` int DEFAULT NULL, -> `LO_SupplyCost` int DEFAULT NULL, -> `LO_Tax` tinyint DEFAULT NULL, -> `LO_CommitDateKey` int DEFAULT NULL, -> `LO_ShipMode` varchar(10) DEFAULT NULL, -> KEY `LO_OrderDateKey` (`LO_OrderDateKey`), -> KEY `LO_OrderDateKey_2` (`LO_OrderDateKey`,`LO_Quantity`,`LO_Discount`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.01 sec) mysql> load data infile '/export/umesh/utils/ssb-dbgen-master/lineorder.tbl' into table lineorderi fields terminated by '|'; Query OK, 6001215 rows affected (7 min 33.22 sec) Records: 6001215 Deleted: 0 Skipped: 0 Warnings: 0 mysql> analyze table lineorderi; +-----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+---------+----------+----------+ | test.lineorderi | analyze | status | OK | +-----------------+---------+----------+----------+ 1 row in set (0.02 sec) mysql> mysql> explain select sum(lo_extendedprice*lo_discount) as revenue from lineorderi where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < 25\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineorderi partitions: NULL type: ALL possible_keys: LO_OrderDateKey,LO_OrderDateKey_2 key: NULL key_len: NULL ref: NULL rows: 5808344 filtered: 1.32 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorderi where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < 25\G *************************** 1. row *************************** revenue: 446031203850 1 row in set (5.28 sec) mysql> explain select sum(lo_extendedprice*lo_discount) as revenue from lineorderi force index (lo_orderdatekey_2) where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < -> 25\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineorderi partitions: NULL type: range possible_keys: LO_OrderDateKey_2 key: LO_OrderDateKey_2 key_len: 7 ref: NULL rows: 1532540 filtered: 3.70 Extra: Using index condition; Using MRR 1 row in set, 1 warning (0.00 sec) mysql> select sum(lo_extendedprice*lo_discount) as revenue from lineorderi force index (lo_orderdatekey_2) where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < 25\G *************************** 1. row *************************** revenue: 446031203850 1 row in set (1.93 sec) mysql> select count(*) from lineorderi force index (lo_orderdatekey_2) where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < 25\G *************************** 1. row *************************** count(*): 118598 1 row in set (0.79 sec) mysql> explain analyze select sum(lo_extendedprice * lo_discount) as revenue from lineorderi force index (lo_orderdatekey_2) where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < 25\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: sum((lineorderi.LO_ExtendedPrice * lineorderi.LO_Discount)) (actual time=1948.634..1948.634 rows=1 loops=1) -> Index range scan on lineorderi using LO_OrderDateKey_2, with index condition: ((lineorderi.LO_OrderDateKey between 19930101 and 19931231) and (lineorderi.LO_Discount between 1 and 3) and (lineorderi.LO_Quantity < 25)) (cost=1629223.34 rows=1532540) (actual time=111.399..1897.538 rows=118598 loops=1) 1 row in set (1.95 sec) mysql> explain analyze select sum(lo_extendedprice * lo_discount) as revenue from lineorderi where lo_orderdatekey between 19930101 and 19931231 and LO_discount between 1 and 3 and LO_quantity < 25\G *************************** 1. row *************************** EXPLAIN: -> Aggregate: sum((lineorderi.LO_ExtendedPrice * lineorderi.LO_Discount)) (actual time=6133.164..6133.165 rows=1 loops=1) -> Filter: ((lineorderi.LO_OrderDateKey between 19930101 and 19931231) and (lineorderi.LO_Discount between 1 and 3) and (lineorderi.LO_Quantity < 25)) (cost=618594.40 rows=76662) (actual time=0.059..6110.183 rows=118598 loops=1) -> Table scan on lineorderi (cost=618594.40 rows=5808344) (actual time=0.039..5340.054 rows=6001215 loops=1) 1 row in set (6.14 sec) mysql> select 1848720 / 6000000 * 100; +-------------------------+ | 1848720 / 6000000 * 100 | +-------------------------+ | 30.8120 | +-------------------------+ 1 row in set (0.00 sec) mysql> system cat docs/INFO_SRC commit: 09901861e2ef411f49aff8905fa99ad479280bf4 date: 2020-06-16 15:51:03 +0200 build-date: 2020-06-16 18:31:54 +0200 short: 0990186 branch: mysql-8.0.21-release MySQL source 8.0.21