MariaDB [tpcds_1]> select * from information_schema.processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | 252 | root | localhost | tpcds_1 | Query | 0 | Filling schema table | select * from information_schema.processlist | 0.552 | 0 | 0 | 0.000 | 85296 | 0 | 1015 | select * from information_schema.processlist | 628 | | 250 | root | localhost | tpcds_1 | Query | 325 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 325237.465 | 0 | 0 | 0.000 | 1713912 | 0 | 950 | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 619 | | 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29640 | | 3 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29635 | | 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29636 | | 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29634 | | 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29633 | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> MariaDB [tpcds_1]> MariaDB [tpcds_1]> MariaDB [tpcds_1]> select * from information_schema.processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | 252 | root | localhost | tpcds_1 | Query | 0 | Filling schema table | select * from information_schema.processlist | 0.424 | 0 | 0 | 0.000 | 85296 | 0 | 1016 | select * from information_schema.processlist | 628 | | 250 | root | localhost | tpcds_1 | Query | 342 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 342365.375 | 0 | 0 | 0.000 | 1967904 | 0 | 950 | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 619 | | 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29640 | | 3 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29635 | | 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29636 | | 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29634 | | 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29633 | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 582 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 1558 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.01 sec) MariaDB [tpcds_1]> select * from information_schema.processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | 252 | root | localhost | tpcds_1 | Query | 0 | Filling schema table | select * from information_schema.processlist | 0.512 | 0 | 0 | 0.000 | 85296 | 0 | 1025 | select * from information_schema.processlist | 628 | | 250 | root | localhost | tpcds_1 | Query | 1629 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 1629735.721 | 0 | 0 | 0.000 | 2348888 | 0 | 950 | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 619 | | 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29640 | | 3 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29635 | | 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29636 | | 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29634 | | 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29633 | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 5743 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 6414 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 6652 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> select * from information_schema.processlist ; +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | 252 | root | localhost | tpcds_1 | Query | 0 | Filling schema table | select * from information_schema.processlist | 0.651 | 0 | 0 | 0.000 | 85296 | 0 | 1032 | select * from information_schema.processlist | 628 | | 250 | root | localhost | tpcds_1 | Query | 6685 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 6685071.439 | 0 | 0 | 0.000 | 3237856 | 0 | 950 | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 619 | | 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29640 | | 3 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29635 | | 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29636 | | 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29634 | | 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29633 | +-----+-------------+-----------+---------+---------+------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.01 sec) MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 14341 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> show processlist ; +-----+-------------+-----------+---------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 14412 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 252 | root | localhost | tpcds_1 | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> MariaDB [tpcds_1]> MariaDB [tpcds_1]> select * from information_schema.processlist ; +-----+-------------+-----------+---------+---------+-------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +-----+-------------+-----------+---------+---------+-------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | 252 | root | localhost | tpcds_1 | Query | 0 | Filling schema table | select * from information_schema.processlist | 0.575 | 0 | 0 | 0.000 | 85296 | 0 | 1037 | select * from information_schema.processlist | 628 | | 250 | root | localhost | tpcds_1 | Query | 14424 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 14424029.786 | 0 | 0 | 0.000 | 3618840 | 0 | 950 | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 619 | | 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29640 | | 3 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29635 | | 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29636 | | 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29634 | | 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29633 | +-----+-------------+-----------+---------+---------+-------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ 7 rows in set (0.00 sec) MariaDB [tpcds_1]> select * from information_schema.processlist ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 253 Current database: tpcds_1 +-----+-------------+-----------+---------+---------+--------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | +-----+-------------+-----------+---------+---------+--------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ | 253 | root | localhost | tpcds_1 | Query | 0 | Filling schema table | select * from information_schema.processlist | 0.352 | 0 | 0 | 0.000 | 85560 | 0 | 1065 | select * from information_schema.processlist | 1245 | | 250 | root | localhost | tpcds_1 | Query | 104473 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 104473063.424 | 0 | 0 | 0.000 | 5398824 | 0 | 950 | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_sales ws1,web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk) select count(distinct ws_order_number) as "order count" ,sum(ws_ext_ship_cost) as "total shipping cost" ,sum(ws_net_profit) as "total net profit" from web_sales ws1 ,date_dim ,customer_address ,web_site where d_date between '1999-2-01' and ADDDATE(cast('1999-2-01' as date),60) and ws1.ws_ship_date_sk = d_date_sk and ws1.ws_ship_addr_sk = ca_address_sk and ca_state = 'IL' and ws1.ws_web_site_sk = web_site_sk and web_company_name = 'pri' and ws1.ws_order_number in (select ws_order_number from ws_wh) and ws1.ws_order_number in (select wr_order_number from web_returns,ws_wh where wr_order_number = ws_wh.ws_order_number) order by count(distinct ws_order_number) limit 100 | 619 | | 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29640 | | 3 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29635 | | 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29636 | | 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29634 | | 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator | NULL | 0.000 | 0 | 0 | 0.000 | 24352 | 0 | 0 | NULL | 29633 | +-----+-------------+-----------+---------+---------+--------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------+-----------+----------+-------------+---------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+ 7 rows in set (0.02 sec) MariaDB [tpcds_1]> SHOW EXPLAIN FOR 250 ; +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ | 1 | PRIMARY | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where | | 1 | PRIMARY | customer_address | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_addr_sk | 1 | Using where | | 1 | PRIMARY | date_dim | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_ship_date_sk | 1 | Using where | | 1 | PRIMARY | web_site | eq_ref | PRIMARY | PRIMARY | 4 | tpcds_1.ws1.ws_web_site_sk | 1 | Using where | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 1 | PRIMARY | | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 3 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | | | 3 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | web_returns | index | NULL | PRIMARY | 8 | NULL | 71763 | Using index | | 4 | MATERIALIZED | ws1 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (flat, BNL join) | | 4 | MATERIALIZED | ws2 | ALL | NULL | NULL | NULL | NULL | 719384 | Using where; Using join buffer (incremental, BNL join) | +------+--------------+------------------+--------+---------------+--------------+---------+-----------------------------+--------+--------------------------------------------------------+ 11 rows in set, 1 warning (0.01 sec) MariaDB [tpcds_1]> Ctrl-C -- exit! Aborted [root@columnstore client]# ps aux | grep mysql root 1262 0.0 0.0 112660 976 pts/2 R+ 18:02 0:00 grep --color=auto mysql root 29294 0.0 0.0 110260 948 pts/2 T Jan12 0:00 less mysqld.1.err zdravel+ 29574 41.7 34.1 7006240 5612564 pts/0 Sl+ Jan12 4271:16 ./mysqld --defaults-file=my2.cnf root 31679 0.0 0.0 165448 8968 pts/1 S+ Jan15 0:00 ./mysql -u root -px [root@columnstore client]# ./mysql -u root -p1 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 254 Server version: 10.2.12-MariaDB-log Source distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show processlist ; +-----+-------------+-----------+---------+---------+--------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+---------+---------+--------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 250 | root | localhost | tpcds_1 | Query | 105012 | Sending data | with ws_wh as (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2 from web_ | 0.000 | | 254 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | +-----+-------------+-----------+---------+---------+--------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 7 rows in set (0.00 sec)