MariaDB [test]> source /home/alice/Desktop/ma2.sql -------------- analyze select ref_0.ol_d_id AS c0, ref_0.ol_supply_w_id AS c1, ref_0.ol_amount AS c2, ref_0.ol_quantity AS c3, ref_0.ol_o_id AS c4, ref_0.ol_o_id AS c5 FROM order_line AS ref_0 WHERE (EXISTS ( SELECT ref_0.ol_amount AS c0, 38 AS c1, ref_1.w_id AS c2, (SELECT w_street_2 FROM warehouse LIMIT 1 OFFSET 4) AS c3, ref_1.w_street_2 AS c4, (SELECT o_entry_d FROM orders LIMIT 1 OFFSET 6) AS c5, ref_1.w_street_2 AS c6, ref_1.w_id AS c7, ref_1.w_street_2 AS c8, ref_0.ol_d_id AS c9 FROM warehouse AS ref_1 WHERE ((ref_1.w_state IS NOT NULL) AND (ref_1.w_zip IS NOT NULL)) OR (ref_1.w_state IS NOT NULL) )) AND (EXISTS ( SELECT ref_2.i_price AS c0, ref_2.i_price AS c1 FROM item AS ref_2 WHERE ref_0.ol_number IS NULL )) -------------- +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 5 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | | 4 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 29406 | NULL | 100.00 | NULL | | | 3 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 5 rows in set (0.466 sec) -------------- analyze select subq_1.c0 as c0, subq_1.c2 as c1, subq_1.c2 as c2, subq_1.c0 as c3, subq_1.c2 as c4, (select o_id from orders limit 1 offset 3) as c5, 19 as c6, subq_1.c2 as c7, subq_1.c1 as c8, subq_1.c2 as c9 from (select distinct cast(nullif(subq_0.c3, subq_0.c1) as character(24)) as c0, subq_0.c3 as c1, subq_0.c4 as c2 from (select ref_0.s_dist_10 as c0, ref_0.s_dist_08 as c1, (select w_name from warehouse limit 1 offset 2) as c2, (select s_dist_10 from stock limit 1 offset 4) as c3, ref_0.s_dist_02 as c4, ref_0.s_dist_01 as c5, 91 as c6, ref_0.s_dist_07 as c7 from stock as ref_0 where ref_0.s_ytd is not NULL) as subq_0 where subq_0.c7 is not NULL) as subq_1 where subq_1.c1 is NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 3 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 6 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | 5.00 | 100.00 | 100.00 | | | 5 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | | 2 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 29406 | NULL | 100.00 | NULL | | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 5 rows in set (0.188 sec) -------------- analyze select 98 as c0, ref_0.d_ytd as c1, ref_0.d_state as c2, ref_0.d_name as c3 from district as ref_0 where (ref_0.d_next_o_id is not NULL) and (EXISTS ( select ref_1.i_price as c0, 53 as c1, ref_0.d_tax as c2, ref_1.i_im_id as c3, cast(nullif(ref_0.d_zip, ref_0.d_zip) as character(9)) as c4, ref_0.d_name as c5, ref_1.i_data as c6, ref_2.o_ol_cnt as c7 from item as ref_1 left join orders as ref_2 on (ref_1.i_im_id = ref_2.o_w_id ) where EXISTS ( select ref_0.d_id as c0, ref_0.d_state as c1, ref_0.d_street_2 as c2 from new_order as ref_3 where (ref_1.i_price is not NULL) and (ref_1.i_name is NULL) ) )) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 29406 | NULL | 100.00 | NULL | Using where; Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 4 rows in set (0.468 sec) -------------- analyze select ref_0.ol_quantity as c0 from order_line as ref_0 where EXISTS ( select ref_0.ol_d_id as c0, ref_0.ol_w_id as c1, ref_0.ol_delivery_d as c2, ref_1.no_o_id as c3, ref_1.no_d_id as c4, ref_0.ol_amount as c5, ref_1.no_w_id as c6, ref_1.no_o_id as c7, ref_0.ol_dist_info as c8 from new_order as ref_1 where ref_0.ol_d_id is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 1.00 | 100.00 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 2 rows in set (0.503 sec) -------------- analyze select ref_0.d_street_2 as c0 from district as ref_0 where EXISTS ( select ref_1.s_quantity as c0, ref_1.s_ytd as c1, ref_0.d_name as c2 from stock as ref_1 where EXISTS ( select ref_2.i_data as c0 from item as ref_2 left join order_line as ref_3 on (4 is not NULL) where ref_1.s_remote_cnt is NULL )) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 3 rows in set (0.330 sec) -------------- analyze select subq_0.c24 as c0, subq_0.c12 as c1, subq_0.c12 as c2 from (select distinct ref_0.c_credit as c0, ref_0.c_zip as c1, ref_0.c_d_id as c2, ref_0.c_state as c3, ref_0.c_delivery_cnt as c4, ref_0.c_credit_lim as c5, ref_0.c_last as c6, ref_0.c_data as c7, ref_0.c_data as c8, cast(nullif(ref_0.c_payment_cnt, ref_0.c_delivery_cnt) as SIGNED) as c9, ref_0.c_state as c10, ref_0.c_since as c11, ref_0.c_credit_lim as c12, 50 as c13, ref_0.c_credit as c14, ref_0.c_last as c15, ref_0.c_w_id as c16, ref_0.c_ytd_payment as c17, ref_0.c_middle as c18, 90 as c19, ref_0.c_city as c20, ref_0.c_zip as c21, ref_0.c_payment_cnt as c22, ref_0.c_credit as c23, ref_0.c_credit as c24 from customer as ref_0 where ref_0.c_city is not NULL ) as subq_0 where subq_0.c22 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 28980 | 30000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 28980 | 30000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+------------------------------+ 2 rows in set (0.495 sec) -------------- analyze select distinct ref_0.s_i_id as c0, ref_0.s_i_id as c1, ref_0.s_data as c2, ref_0.s_dist_03 as c3 from stock as ref_0 where ref_0.s_dist_04 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.087 sec) -------------- analyze select (select s_dist_01 from stock limit 1 offset 8) as c0, ref_0.s_remote_cnt as c1, ref_0.s_quantity as c2, (select no_o_id from new_order limit 1 offset 6) as c3, cast(nullif(ref_0.s_dist_10, cast(nullif(ref_0.s_dist_07, ref_0.s_dist_08) as character(24))) as character(24)) as c4, ref_0.s_dist_03 as c5, ref_0.s_data as c6, (select s_order_cnt from stock limit 1 offset 4) as c7, ref_0.s_dist_03 as c8, ref_0.s_i_id as c9, ref_0.s_dist_08 as c10, ref_0.s_dist_03 as c11 from stock as ref_0 where EXISTS ( select ref_1.c_middle as c0, ref_1.c_balance as c1, 30 as c2, ref_0.s_dist_10 as c3, ref_1.c_balance as c4 from customer as ref_1 where ref_0.s_quantity is not NULL) -------------- +------+--------------------+-----------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-----------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 5 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 28980 | 1.00 | 100.00 | 100.00 | | | 4 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | 5.00 | 100.00 | 100.00 | | | 3 | SUBQUERY | new_order | ALL | NULL | NULL | NULL | NULL | 7999 | 7.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | 9.00 | 100.00 | 100.00 | | +------+--------------------+-----------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ 5 rows in set (0.268 sec) -------------- analyze select 14 as c0, subq_0.c4 as c1, ref_0.s_ytd as c2 from stock as ref_0 left join (select ref_1.s_dist_09 as c0, ref_1.s_remote_cnt as c1, ref_1.s_dist_06 as c2, ref_1.s_dist_09 as c3, ref_1.s_data as c4, ref_1.s_quantity as c5, ref_1.s_dist_04 as c6, ref_1.s_dist_02 as c7 from stock as ref_1 where ref_1.s_i_id is NULL) as subq_0 on (ref_0.s_dist_05 = subq_0.c0 ) where subq_0.c0 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 2 rows in set (0.449 sec) -------------- analyze select subq_0.c4 as c0, subq_0.c0 as c1, cast(nullif(subq_0.c4, subq_0.c4) as SIGNED) as c2, subq_0.c0 as c3, subq_0.c3 as c4 from (select ref_1.ol_number as c0, ref_1.ol_amount as c1, ref_1.ol_i_id as c2, ref_1.ol_number as c3, ref_0.d_id as c4 from district as ref_0 left join order_line as ref_1 on (ref_1.ol_amount is not NULL) where EXISTS ( select ref_1.ol_supply_w_id as c0, ref_1.ol_i_id as c1, ref_3.ol_amount as c2, ref_3.ol_number as c3 from district as ref_2 inner join order_line as ref_3 on (EXISTS ( select ref_4.ol_o_id as c0, ref_2.d_zip as c1, ref_3.ol_number as c2, ref_3.ol_w_id as c3, ref_2.d_zip as c4, 30 as c5, ref_0.d_tax as c6, ref_3.ol_w_id as c7, ref_1.ol_number as c8, ref_2.d_street_2 as c9 from order_line as ref_4 where 61 is NULL )) where ref_2.d_tax is NULL) ) as subq_0 where subq_0.c1 is not NULL -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 4 rows in set (0.524 sec) -------------- analyze select ref_0.ol_i_id as c0, ref_1.d_state as c1, 91 as c2, ref_0.ol_o_id as c3, ref_1.d_city as c4, ref_0.ol_o_id as c5, ref_0.ol_number as c6, ref_0.ol_amount as c7 from order_line as ref_0 inner join district as ref_1 on (EXISTS ( select ref_2.h_amount as c0, ref_1.d_name as c1, ref_2.h_data as c2, ref_2.h_d_id as c3, ref_1.d_next_o_id as c4, ref_0.ol_delivery_d as c5, ref_1.d_street_1 as c6 from history as ref_2 where ref_0.ol_supply_w_id is NULL)) where (ref_1.d_city is not NULL) or (cast(coalesce(ref_0.ol_o_id, ref_0.ol_i_id) as SIGNED) is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.741 sec) -------------- analyze select distinct ref_0.s_data as c0 from stock as ref_0 where ref_0.s_dist_10 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.075 sec) -------------- analyze select distinct ref_0.i_id as c0, ref_0.i_name as c1, ref_0.i_name as c2, ref_0.i_price as c3, (select o_carrier_id from orders limit 1 offset 4) as c4, ref_0.i_im_id as c5 from item as ref_0 where ((select o_w_id from orders limit 1 offset 1) is not NULL) and (ref_0.i_id is not NULL) -------------- +------+-------------+--------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+--------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 3 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 29406 | 2.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 29406 | 5.00 | 100.00 | 100.00 | | +------+-------------+--------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 3 rows in set (0.094 sec) -------------- analyze select ref_0.ol_i_id as c0, ref_1.d_state as c1, 91 as c2, ref_0.ol_o_id as c3, ref_1.d_city as c4, ref_0.ol_o_id as c5, ref_0.ol_number as c6, ref_0.ol_amount as c7 from order_line as ref_0 inner join district as ref_1 on (EXISTS ( select ref_2.h_amount as c0, ref_1.d_name as c1, ref_2.h_data as c2, ref_2.h_d_id as c3, ref_1.d_next_o_id as c4, ref_0.ol_delivery_d as c5, ref_1.d_street_1 as c6 from history as ref_2 where ref_0.ol_supply_w_id is NULL)) where (ref_1.d_city is not NULL) or (cast(coalesce(ref_0.ol_o_id, ref_0.ol_i_id) as SIGNED) is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.633 sec) -------------- analyze select distinct ref_0.i_price as c0, ref_0.i_price as c1, ref_0.i_data as c2, ref_0.i_id as c3 from item as ref_0 where ref_0.i_data is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.099 sec) -------------- analyze select subq_0.c0 as c0, 66 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, 52 as c5 from (select ref_0.c_data as c0 from customer as ref_0 where 92 is not NULL) as subq_0 where (EXISTS ( select ref_2.no_w_id as c0, ref_1.d_city as c1, ref_2.no_w_id as c2, subq_0.c0 as c3, 100 as c4 from district as ref_1 inner join new_order as ref_2 on (ref_1.d_id = ref_2.no_w_id ) where (subq_0.c0 is not NULL) or ((ref_1.d_w_id is not NULL) or (ref_1.d_state is not NULL)))) and (subq_0.c0 is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 28980 | 30000.00 | 100.00 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 7999 | 1.00 | 100.00 | 14.29 | Using where; Using join buffer (flat, BNL join) | +------+--------------------+-------+------+---------------+------+---------+------+-------+----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.229 sec) -------------- analyze select distinct ref_0.i_id as c0, ref_0.i_im_id as c1, ref_0.i_price as c2, ref_0.i_data as c3, cast(coalesce(ref_0.i_im_id, ref_0.i_id) as SIGNED) as c4 from item as ref_0 where ref_0.i_price is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.107 sec) -------------- analyze select ref_0.d_street_1 as c0, ref_0.d_street_1 as c1, ref_0.d_street_1 as c2 from district as ref_0 where EXISTS ( select subq_0.c0 as c0, subq_0.c0 as c1, ref_0.d_w_id as c2 from (select ref_1.s_dist_08 as c0 from stock as ref_1 where ref_1.s_dist_09 is not NULL) as subq_0 where EXISTS ( select ref_0.d_w_id as c0, subq_0.c0 as c1, ref_2.c_data as c2, subq_0.c0 as c3, ref_0.d_state as c4 from customer as ref_2 where ref_0.d_city is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 3 rows in set (0.463 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select ref_0.ol_number as c0 from order_line as ref_0 where EXISTS ( select ref_0.ol_w_id as c0, ref_0.ol_amount as c1, ref_1.h_c_id as c2, ref_1.h_date as c3, 4 as c4, ref_0.ol_i_id as c5, ref_0.ol_o_id as c6, ref_0.ol_supply_w_id as c7, 41 as c8, ref_0.ol_quantity as c9, ref_1.h_c_id as c10, ref_0.ol_i_id as c11, ref_0.ol_number as c12, 51 as c13 from history as ref_1 where 22 is NULL ) ) as subq_0 where EXISTS ( select ref_2.c_balance as c0, ref_2.c_discount as c1, subq_0.c0 as c2, ref_2.c_street_1 as c3, subq_0.c0 as c4, subq_0.c0 as c5, ref_2.c_d_id as c6, ref_2.c_street_1 as c7 from customer as ref_2 where ref_2.c_state is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 28980 | 1.00 | 100.00 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 3 rows in set (0.713 sec) -------------- analyze select (select i_price from item limit 1 offset 1) as c0, subq_0.c6 as c1, subq_0.c4 as c2, subq_0.c1 as c3, subq_0.c2 as c4, subq_0.c4 as c5, subq_0.c2 as c6 from (select distinct ref_0.s_dist_02 as c0, ref_0.s_i_id as c1, ref_0.s_dist_05 as c2, ref_0.s_dist_07 as c3, ref_0.s_dist_04 as c4, (select o_d_id from orders limit 1 offset 4) as c5, ref_0.s_w_id as c6 from stock as ref_0 where ref_0.s_dist_08 is not NULL) as subq_0 where subq_0.c5 is NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 3 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 4 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 29406 | 5.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | NULL | 100.00 | NULL | | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 4 rows in set (0.325 sec) -------------- analyze select (select ol_quantity from order_line limit 1 offset 2) as c0, subq_0.c1 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c3 as c4, subq_0.c0 as c5 from (select distinct ref_0.s_dist_07 as c0, (select h_data from history limit 1 offset 1) as c1, cast(coalesce(ref_0.s_dist_06, ref_0.s_dist_08) as character(24)) as c2, ref_0.s_dist_02 as c3, ref_0.s_data as c4 from stock as ref_0 where ref_0.s_dist_02 is not NULL) as subq_0 where EXISTS ( select subq_0.c4 as c0, ref_1.d_id as c1, subq_0.c3 as c2 from district as ref_1 where subq_0.c1 is NULL ) -------------- +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 5 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | | | 3 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 4 | SUBQUERY | history | ALL | NULL | NULL | NULL | NULL | 30056 | 2.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 5 rows in set (0.782 sec) -------------- analyze select subq_0.c1 as c0 from (select distinct ref_0.s_data as c0, ref_0.s_order_cnt as c1, ref_0.s_dist_10 as c2, ref_0.s_dist_07 as c3 from stock as ref_0 where (ref_0.s_order_cnt is not NULL) and (32 is not NULL) ) as subq_0 where subq_0.c3 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.112 sec) -------------- analyze select distinct ref_0.s_dist_03 as c0, ref_0.s_data as c1 from stock as ref_0 where ref_0.s_remote_cnt is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.089 sec) -------------- analyze select distinct 57 as c0, ref_0.i_id as c1, ref_0.i_data as c2, ref_0.i_im_id as c3, ref_0.i_data as c4, ref_0.i_im_id as c5, ref_0.i_name as c6 from item as ref_0 where ref_0.i_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.343 sec) -------------- analyze select 97 as c0, subq_0.c1 as c1, subq_0.c1 as c2, subq_0.c1 as c3 from (select distinct ref_0.i_data as c0, ref_0.i_im_id as c1 from item as ref_0 where EXISTS ( select ref_0.i_price as c0, ref_1.s_dist_09 as c1 from stock as ref_1 where EXISTS ( select ref_0.i_im_id as c0, ref_1.s_quantity as c1, ref_0.i_name as c2, ref_1.s_dist_08 as c3, 86 as c4, ref_0.i_data as c5, ref_0.i_im_id as c6, 23 as c7, ref_0.i_price as c8, 69 as c9, ref_2.h_c_w_id as c10 from history as ref_2 where ref_2.h_date is not NULL)) ) as subq_0 where 25 is not NULL -------------- +------+--------------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 1.00 | 100.00 | 100.00 | Using where | | 4 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 30056 | 1.00 | 100.00 | 100.00 | Using where | +------+--------------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 4 rows in set (0.340 sec) -------------- analyze select ref_0.d_w_id as c0 from district as ref_0 where EXISTS ( select ref_0.d_id as c0, ref_0.d_street_2 as c1, ref_1.s_dist_08 as c2, ref_1.s_dist_10 as c3, ref_0.d_city as c4 from stock as ref_1 where (ref_0.d_name is not NULL) and ((EXISTS ( select ref_1.s_dist_01 as c0, ref_1.s_dist_02 as c1 from item as ref_2 where ref_1.s_dist_06 is NULL )) and (ref_1.s_i_id is not NULL))) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 3 rows in set (0.476 sec) -------------- analyze select ref_0.d_street_1 as c0, ref_0.d_street_1 as c1, ref_0.d_street_1 as c2 from district as ref_0 where EXISTS ( select subq_0.c0 as c0, subq_0.c0 as c1, ref_0.d_w_id as c2 from (select ref_1.s_dist_08 as c0 from stock as ref_1 where ref_1.s_dist_09 is not NULL) as subq_0 where EXISTS ( select ref_0.d_w_id as c0, subq_0.c0 as c1, ref_2.c_data as c2, subq_0.c0 as c3, ref_0.d_state as c4 from customer as ref_2 where ref_0.d_city is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 3 rows in set (0.509 sec) -------------- analyze select distinct ref_0.i_im_id as c0, ref_0.i_data as c1, ref_0.i_im_id as c2, ref_0.i_name as c3, case when ref_0.i_id is not NULL then ref_0.i_name else ref_0.i_name end as c4 from item as ref_0 where ref_0.i_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.126 sec) -------------- analyze select distinct ref_0.s_data as c0, ref_0.s_dist_05 as c1 from stock as ref_0 where ref_0.s_dist_07 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.100 sec) -------------- analyze select (select ol_quantity from order_line limit 1 offset 2) as c0, subq_0.c1 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c3 as c4, subq_0.c0 as c5 from (select distinct ref_0.s_dist_07 as c0, (select h_data from history limit 1 offset 1) as c1, cast(coalesce(ref_0.s_dist_06, ref_0.s_dist_08) as character(24)) as c2, ref_0.s_dist_02 as c3, ref_0.s_data as c4 from stock as ref_0 where ref_0.s_dist_02 is not NULL) as subq_0 where EXISTS ( select subq_0.c4 as c0, ref_1.d_id as c1, subq_0.c3 as c2 from district as ref_1 where subq_0.c1 is NULL ) -------------- +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 5 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | | | 3 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 4 | SUBQUERY | history | ALL | NULL | NULL | NULL | NULL | 30056 | 2.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 5 rows in set (0.742 sec) -------------- analyze select ref_0.ol_delivery_d as c0, ref_0.ol_dist_info as c1, ref_0.ol_dist_info as c2 from order_line as ref_0 where EXISTS ( select ref_1.ol_supply_w_id as c0, ref_1.ol_supply_w_id as c1, ref_0.ol_amount as c2, ref_0.ol_amount as c3, ref_1.ol_supply_w_id as c4 from order_line as ref_1 where ref_0.ol_supply_w_id is not NULL ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 1.00 | 100.00 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 2 rows in set (0.448 sec) -------------- analyze select distinct ref_0.i_data as c0, ref_0.i_price as c1, ref_0.i_price as c2, ref_0.i_price as c3, ref_0.i_price as c4 from item as ref_0 where EXISTS ( select ref_1.w_name as c0, ref_0.i_price as c1, ref_1.w_zip as c2, ref_1.w_city as c3, ref_0.i_id as c4, ref_1.w_street_1 as c5, ref_1.w_zip as c6, ref_1.w_zip as c7, ref_1.w_id as c8, ref_0.i_price as c9, ref_0.i_data as c10, (select w_id from warehouse limit 1 offset 2) as c11, 17 as c12 from warehouse as ref_1 where ref_1.w_id is not NULL ) -------------- +------+--------------------+-----------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-----------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | | 3 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | +------+--------------------+-----------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 3 rows in set (0.213 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1 from (select distinct ref_0.i_name as c0, ref_0.i_name as c1 from item as ref_0 where (ref_0.i_data is not NULL) or (ref_0.i_id is not NULL) ) as subq_0 where subq_0.c1 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.088 sec) -------------- analyze select subq_1.c0 as c0, subq_1.c0 as c1, subq_1.c0 as c2, 16 as c3 from (select subq_0.c0 as c0 from stock as ref_0 inner join (select ref_1.s_ytd as c0, ref_1.s_dist_04 as c1 from stock as ref_1 where EXISTS ( select ref_1.s_dist_09 as c0 from stock as ref_2 where ref_1.s_dist_08 is NULL ) ) as subq_0 on (ref_0.s_dist_02 = subq_0.c1 ) where ref_0.s_ytd is not NULL ) as subq_1 where ((subq_1.c0 is not NULL) and (subq_1.c0 is not NULL)) and (subq_1.c0 is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.705 sec) -------------- analyze select distinct ref_0.i_price as c0, ref_0.i_name as c1, ref_0.i_data as c2, ref_0.i_id as c3 from item as ref_0 where ref_0.i_price is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.091 sec) -------------- analyze select ref_0.ol_w_id as c0, ref_0.ol_i_id as c1, ref_0.ol_dist_info as c2 from order_line as ref_0 where EXISTS ( select 84 as c0, ref_0.ol_supply_w_id as c1, ref_1.i_price as c2, ref_1.i_name as c3, ref_1.i_name as c4 from item as ref_1 where ref_0.ol_amount is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 99438 | 1.00 | 100.00 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 2 rows in set (0.498 sec) -------------- analyze select ref_0.i_data as c0 from item as ref_0 inner join (select (select i_name from item limit 1 offset 1) as c0 from stock as ref_1 where ref_1.s_dist_06 is not NULL) as subq_0 on (ref_0.i_name = subq_0.c0 ) where (ref_0.i_im_id is not NULL) or (ref_0.i_im_id is not NULL) -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 3 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 2.00 | 100.00 | 100.00 | | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.458 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c2 as c1, subq_0.c0 as c2 from (select ref_0.w_state as c0, ref_0.w_ytd as c1, ref_0.w_city as c2, ref_0.w_tax as c3, ref_0.w_ytd as c4, case when (ref_0.w_city is not NULL) and (ref_0.w_street_1 is NULL) then ref_0.w_name else ref_0.w_name end as c5, ref_0.w_ytd as c6 from warehouse as ref_0 where (ref_0.w_city is not NULL) and (EXISTS ( select 16 as c0, ref_0.w_street_2 as c1, ref_1.d_state as c2, ref_1.d_zip as c3, ref_1.d_street_2 as c4, ref_1.d_id as c5, ref_2.ol_o_id as c6, ref_0.w_city as c7, ref_1.d_zip as c8, ref_1.d_ytd as c9, ref_0.w_street_2 as c10, ref_1.d_tax as c11, ref_1.d_w_id as c12, ref_1.d_street_1 as c13 from district as ref_1 inner join order_line as ref_2 on (EXISTS ( select ref_1.d_street_1 as c0, 36 as c1, ref_2.ol_w_id as c2, ref_3.h_c_d_id as c3, ref_1.d_ytd as c4, ref_2.ol_d_id as c5, ref_1.d_city as c6, ref_3.h_w_id as c7, ref_3.h_date as c8, 1 as c9, (select i_id from item limit 1 offset 48) as c10 from history as ref_3 where ref_1.d_zip is NULL)) where ref_2.ol_i_id is not NULL ))) as subq_0 where case when subq_0.c0 is NULL then subq_0.c6 else subq_0.c6 end is not NULL -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | | | 3 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 5 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 5 rows in set (0.710 sec) -------------- analyze select ref_1.ol_number as c0, ref_1.ol_dist_info as c1, ref_0.c_last as c2 from customer as ref_0 inner join order_line as ref_1 on (((ref_0.c_delivery_cnt is NULL) and (ref_0.c_middle is NULL)) or (ref_1.ol_o_id is not NULL)) where cast(nullif((select i_im_id from item limit 1 offset 3) , ref_1.ol_d_id) as SIGNED) is NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 28980 | 30000.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 2 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 4.00 | 100.00 | 100.00 | | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.366 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c2 as c1, subq_0.c0 as c2 from (select ref_0.w_state as c0, ref_0.w_ytd as c1, ref_0.w_city as c2, ref_0.w_tax as c3, ref_0.w_ytd as c4, case when (ref_0.w_city is not NULL) and (ref_0.w_street_1 is NULL) then ref_0.w_name else ref_0.w_name end as c5, ref_0.w_ytd as c6 from warehouse as ref_0 where (ref_0.w_city is not NULL) and (EXISTS ( select 16 as c0, ref_0.w_street_2 as c1, ref_1.d_state as c2, ref_1.d_zip as c3, ref_1.d_street_2 as c4, ref_1.d_id as c5, ref_2.ol_o_id as c6, ref_0.w_city as c7, ref_1.d_zip as c8, ref_1.d_ytd as c9, ref_0.w_street_2 as c10, ref_1.d_tax as c11, ref_1.d_w_id as c12, ref_1.d_street_1 as c13 from district as ref_1 inner join order_line as ref_2 on (EXISTS ( select ref_1.d_street_1 as c0, 36 as c1, ref_2.ol_w_id as c2, ref_3.h_c_d_id as c3, ref_1.d_ytd as c4, ref_2.ol_d_id as c5, ref_1.d_city as c6, ref_3.h_w_id as c7, ref_3.h_date as c8, 1 as c9, (select i_id from item limit 1 offset 48) as c10 from history as ref_3 where ref_1.d_zip is NULL)) where ref_2.ol_i_id is not NULL ))) as subq_0 where case when subq_0.c0 is NULL then subq_0.c6 else subq_0.c6 end is not NULL -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | | | 3 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 5 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 5 rows in set (0.703 sec) -------------- analyze select subq_0.c3 as c0, subq_0.c2 as c1, subq_0.c8 as c2 from (select distinct ref_0.i_data as c0, ref_0.i_data as c1, ref_0.i_name as c2, ref_0.i_name as c3, ref_0.i_id as c4, ref_0.i_name as c5, 65 as c6, ref_0.i_data as c7, ref_0.i_data as c8, ref_0.i_im_id as c9, ref_0.i_im_id as c10, ref_0.i_id as c11 from item as ref_0 where ref_0.i_id is not NULL ) as subq_0 where (subq_0.c0 is not NULL) and ((select i_data from item limit 1 offset 2) is not NULL) -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where | | 3 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 3.00 | 100.00 | 100.00 | | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 3 rows in set (1.095 sec) -------------- analyze select distinct ref_0.s_data as c0, ref_0.s_dist_02 as c1, ref_0.s_data as c2 from stock as ref_0 where ref_0.s_w_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.106 sec) -------------- analyze select subq_0.c1 as c0 from (select distinct ref_0.s_data as c0, ref_0.s_order_cnt as c1, ref_0.s_dist_10 as c2, ref_0.s_dist_07 as c3 from stock as ref_0 where (ref_0.s_order_cnt is not NULL) and (32 is not NULL) ) as subq_0 where subq_0.c3 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.137 sec) -------------- analyze select distinct ref_0.i_data as c0, ref_0.i_data as c1, ref_0.i_price as c2 from item as ref_0 where ref_0.i_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.091 sec) -------------- analyze select subq_0.c2 as c0, 22 as c1, subq_0.c2 as c2, subq_0.c3 as c3, subq_0.c3 as c4, subq_0.c3 as c5, subq_0.c1 as c6, subq_0.c3 as c7, subq_0.c3 as c8, (select ol_number from order_line limit 1 offset 2) as c9, subq_0.c2 as c10 from (select ref_1.i_im_id as c0, ref_1.i_price as c1, ref_0.i_data as c2, ref_1.i_price as c3 from item as ref_0 left join item as ref_1 on (ref_0.i_name = ref_1.i_name ) where ref_1.i_data is not NULL ) as subq_0 where subq_0.c1 is NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 2 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | | +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.709 sec) -------------- analyze select ref_0.s_dist_02 as c0, ref_0.s_remote_cnt as c1, ref_0.s_remote_cnt as c2 from stock as ref_0 where EXISTS ( select ref_0.s_dist_07 as c0, ref_0.s_dist_10 as c1 from district as ref_1 inner join warehouse as ref_2 on (ref_1.d_tax = ref_2.w_tax ) where ref_2.w_ytd is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.668 sec) -------------- analyze select distinct ref_0.i_data as c0, ref_0.i_name as c1, ref_0.i_name as c2 from item as ref_0 where ref_0.i_im_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.084 sec) -------------- analyze select subq_0.c2 as c0, subq_0.c2 as c1, subq_0.c0 as c2 from (select distinct ref_0.i_id as c0, cast(nullif(ref_0.i_im_id, ref_0.i_im_id) as SIGNED) as c1, ref_0.i_name as c2, ref_0.i_data as c3 from item as ref_0 where ref_0.i_im_id is not NULL ) as subq_0 where subq_0.c2 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.107 sec) -------------- analyze select subq_0.c2 as c0, subq_0.c0 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c1 as c4 from (select (select d_street_2 from district limit 1 offset 5) as c0, ref_0.w_state as c1, ref_0.w_street_1 as c2, ref_0.w_zip as c3 from warehouse as ref_0 where EXISTS ( select ref_1.i_id as c0, ref_1.i_price as c1 from item as ref_1 where ref_0.w_city is not NULL ) ) as subq_0 where EXISTS ( select ref_2.o_ol_cnt as c0, ref_3.no_d_id as c1 from orders as ref_2 inner join new_order as ref_3 on (ref_2.o_c_id = ref_3.no_w_id ) where ref_2.o_d_id is not NULL) -------------- +------+--------------------+----------+------+---------------+------+---------+------+-------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+------+---------------+------+---------+------+-------+---------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | Using where | | 5 | SUBQUERY | ref_3 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | | | 5 | SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 29406 | 2964.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 4 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 99438 | 1.00 | 100.00 | 100.00 | | | 3 | SUBQUERY | district | ALL | NULL | NULL | NULL | NULL | 10 | 6.00 | 100.00 | 100.00 | | +------+--------------------+----------+------+---------------+------+---------+------+-------+---------+----------+------------+-------------------------------------------------+ 5 rows in set (1.027 sec) -------------- analyze select ref_0.d_zip as c0, ref_0.d_w_id as c1 from district as ref_0 where ((ref_0.d_next_o_id is not NULL) or (ref_0.d_tax is not NULL)) and (EXISTS ( select ref_1.ol_number as c0, (select s_dist_02 from stock limit 1 offset 2) as c1, ref_0.d_w_id as c2 from order_line as ref_1 where EXISTS ( select ref_2.w_zip as c0, ref_1.ol_w_id as c1, ref_1.ol_amount as c2, ref_0.d_city as c3, ref_0.d_street_1 as c4 from warehouse as ref_2 where ref_0.d_state is NULL))) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 3 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 4 rows in set (1.326 sec) -------------- analyze select subq_0.c1 as c0 from (select distinct ref_0.s_dist_07 as c0, ref_0.s_i_id as c1, ref_0.s_order_cnt as c2, ref_0.s_dist_04 as c3, ref_0.s_dist_09 as c4, ref_0.s_quantity as c5 from stock as ref_0 where ref_0.s_remote_cnt is not NULL ) as subq_0 where 37 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.145 sec) -------------- analyze select 25 as c0, subq_0.c13 as c1 from (select distinct ref_0.i_im_id as c0, ref_0.i_id as c1, 68 as c2, ref_0.i_im_id as c3, ref_0.i_data as c4, ref_0.i_im_id as c5, ref_0.i_im_id as c6, ref_0.i_im_id as c7, ref_0.i_im_id as c8, ref_0.i_name as c9, ref_0.i_price as c10, ref_0.i_price as c11, ref_0.i_price as c12, ref_0.i_price as c13, ref_0.i_im_id as c14, ref_0.i_price as c15, ref_0.i_id as c16 from item as ref_0 where ref_0.i_im_id is not NULL ) as subq_0 where subq_0.c0 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.197 sec) -------------- analyze select (select c_payment_cnt from customer limit 1 offset 48) as c0, ref_1.d_state as c1, ref_0.ol_w_id as c2 from order_line as ref_0 inner join district as ref_1 on (EXISTS ( select ref_0.ol_number as c0 from customer as ref_2 where ref_1.d_next_o_id is NULL )) where ref_0.ol_amount is not NULL -------------- +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 2 | SUBQUERY | customer | ALL | NULL | NULL | NULL | NULL | 28980 | NULL | 100.00 | NULL | | +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 4 rows in set (0.710 sec) -------------- analyze select cast(coalesce(ref_0.ol_number, ref_0.ol_o_id) as SIGNED) as c0, ref_0.ol_delivery_d as c1, ref_1.d_street_1 as c2, ref_0.ol_dist_info as c3, ref_0.ol_i_id as c4, ref_0.ol_supply_w_id as c5, ref_0.ol_i_id as c6, ref_1.d_id as c7 from order_line as ref_0 left join district as ref_1 on (ref_0.ol_w_id = ref_1.d_w_id ) where EXISTS ( select ref_0.ol_number as c0, ref_1.d_w_id as c1, ref_1.d_name as c2, ref_1.d_w_id as c3, ref_0.ol_d_id as c4 from history as ref_2 where (ref_2.h_w_id is not NULL) and (ref_1.d_name is NULL) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 10.00 | Using where; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 30056 | NULL | 100.00 | NULL | Using where | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.973 sec) -------------- analyze select ref_0.ol_amount as c0, ref_0.ol_w_id as c1, ref_0.ol_w_id as c2, ref_0.ol_w_id as c3, ref_0.ol_o_id as c4, ref_0.ol_dist_info as c5, ref_0.ol_amount as c6 from order_line as ref_0 where (EXISTS ( select ref_0.ol_w_id as c0, 50 as c1, ref_1.s_w_id as c2, ref_0.ol_o_id as c3, ref_1.s_remote_cnt as c4, ref_1.s_dist_08 as c5, ref_1.s_dist_04 as c6 from stock as ref_1 where ref_0.ol_delivery_d is not NULL)) and (ref_0.ol_supply_w_id is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 1.00 | 100.00 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 2 rows in set (0.537 sec) -------------- analyze select ref_0.s_dist_09 as c0, ref_0.s_order_cnt as c1 from stock as ref_0 where (EXISTS ( select ref_0.s_dist_05 as c0, ref_0.s_dist_06 as c1, ref_1.d_city as c2, ref_0.s_dist_04 as c3, ref_0.s_dist_01 as c4, ref_1.d_ytd as c5, ref_1.d_tax as c6, ref_1.d_street_2 as c7, ref_0.s_dist_06 as c8, ref_1.d_state as c9 from district as ref_1 where cast(nullif(ref_1.d_w_id, ref_1.d_next_o_id) as SIGNED) is NULL )) and (ref_0.s_dist_02 is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ 2 rows in set (0.543 sec) -------------- analyze select distinct subq_0.c1 as c0, subq_0.c0 as c1, subq_0.c1 as c2, 60 as c3, subq_0.c0 as c4, subq_0.c0 as c5, subq_0.c0 as c6 from (select ref_0.i_name as c0, ref_0.i_im_id as c1 from item as ref_0 where ref_0.i_name is not NULL) as subq_0 where cast(coalesce(subq_0.c1, subq_0.c1) as SIGNED) is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.084 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1 from (select ref_0.s_dist_10 as c0 from stock as ref_0 where ref_0.s_remote_cnt is not NULL) as subq_0 where EXISTS ( select subq_0.c0 as c0, ref_1.d_w_id as c1, subq_0.c0 as c2, subq_0.c0 as c3, ref_1.d_id as c4, ref_1.d_zip as c5, subq_0.c0 as c6, subq_0.c0 as c7, subq_0.c0 as c8, 99 as c9, 18 as c10, ref_1.d_state as c11 from district as ref_1 where EXISTS ( select ref_2.w_id as c0, subq_0.c0 as c1, ref_1.d_ytd as c2, 26 as c3, ref_1.d_name as c4, ref_1.d_state as c5, ref_1.d_id as c6, subq_0.c0 as c7, ref_2.w_id as c8, ref_2.w_street_2 as c9, ref_2.w_tax as c10, subq_0.c0 as c11, ref_1.d_tax as c12, ref_2.w_street_2 as c13, ref_1.d_street_1 as c14 from warehouse as ref_2 where 39 is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 3 rows in set (0.648 sec) -------------- analyze select subq_0.c0 as c0 from (select ref_1.ol_supply_w_id as c0, (select w_tax from warehouse limit 1 offset 3) as c1, ref_0.s_dist_09 as c2, ref_1.ol_i_id as c3 from stock as ref_0 left join order_line as ref_1 on (ref_0.s_dist_09 = ref_1.ol_dist_info ) where ref_1.ol_supply_w_id is not NULL ) as subq_0 where subq_0.c3 is NULL -------------- +------+-------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 3 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | +------+-------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (1.052 sec) -------------- analyze select ref_0.d_street_2 as c0 from district as ref_0 where EXISTS ( select ref_1.s_quantity as c0, ref_1.s_ytd as c1, ref_0.d_name as c2 from stock as ref_1 where EXISTS ( select ref_2.i_data as c0 from item as ref_2 left join order_line as ref_3 on (4 is not NULL) where ref_1.s_remote_cnt is NULL )) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where | | 3 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 3 rows in set (0.385 sec) -------------- analyze select ref_0.ol_number as c0, ref_0.ol_i_id as c1, (select i_data from item limit 1 offset 5) as c2, ref_0.ol_o_id as c3, ref_0.ol_i_id as c4, ref_0.ol_quantity as c5, ref_0.ol_dist_info as c6 from order_line as ref_0 where (((EXISTS ( select ref_1.c_d_id as c0, ref_0.ol_dist_info as c1, ref_0.ol_dist_info as c2, ref_1.c_balance as c3, ref_0.ol_dist_info as c4, ref_1.c_credit as c5, ref_1.c_street_2 as c6, 75 as c7, ref_1.c_payment_cnt as c8, ref_0.ol_dist_info as c9, ref_0.ol_w_id as c10 from customer as ref_1 where ref_0.ol_quantity is not NULL)) and (ref_0.ol_number is not NULL)) and ((ref_0.ol_quantity is not NULL) and (ref_0.ol_w_id is not NULL))) and ((ref_0.ol_i_id is not NULL) or (ref_0.ol_w_id is not NULL)) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 28980 | 1.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 6.00 | 100.00 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 3 rows in set (0.611 sec) -------------- analyze select subq_0.c2 as c0, subq_0.c0 as c1, subq_0.c1 as c2, subq_0.c1 as c3, subq_0.c1 as c4 from (select (select d_street_2 from district limit 1 offset 5) as c0, ref_0.w_state as c1, ref_0.w_street_1 as c2, ref_0.w_zip as c3 from warehouse as ref_0 where EXISTS ( select ref_1.i_id as c0, ref_1.i_price as c1 from item as ref_1 where ref_0.w_city is NULL ) ) as subq_0 where EXISTS ( select ref_2.o_ol_cnt as c0, ref_3.no_d_id as c1 from orders as ref_2 inner join new_order as ref_3 on (ref_2.o_c_id = ref_3.no_w_id ) where ref_2.o_d_id is not NULL) -------------- +------+-------------+----------+------+---------------+------+---------+------+-------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+----------+------+---------------+------+---------+------+-------+---------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 0.00 | Using where | | 5 | SUBQUERY | ref_3 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | | | 5 | SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 29406 | 2964.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 4 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 3 | SUBQUERY | district | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | | +------+-------------+----------+------+---------------+------+---------+------+-------+---------+----------+------------+-------------------------------------------------+ 5 rows in set (1.000 sec) -------------- analyze select distinct subq_0.c2 as c0, (select w_ytd from warehouse limit 1 offset 3) as c1, subq_0.c2 as c2, subq_0.c2 as c3, subq_0.c2 as c4, subq_0.c1 as c5, cast(coalesce(subq_0.c1, subq_0.c0) as character(24)) as c6, subq_0.c0 as c7, subq_0.c1 as c8, subq_0.c1 as c9, subq_0.c1 as c10, subq_0.c2 as c11, (select no_o_id from new_order limit 1 offset 1) as c12 from (select ref_0.s_dist_10 as c0, ref_0.s_dist_07 as c1, ref_0.s_w_id as c2 from stock as ref_0 where (select ol_o_id from order_line limit 1 offset 6) is not NULL) as subq_0 where subq_0.c2 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 5 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | 7.00 | 100.00 | 100.00 | | | 3 | SUBQUERY | new_order | ALL | NULL | NULL | NULL | NULL | 7999 | 2.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | | +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 4 rows in set (0.867 sec) -------------- analyze select subq_0.c1 as c0, (select ol_amount from order_line limit 1 offset 5) as c1, case when subq_0.c11 is not NULL then subq_0.c4 else subq_0.c4 end as c2, subq_0.c3 as c3 from (select distinct (select s_ytd from stock limit 1 offset 6) as c0, ref_0.s_dist_06 as c1, 82 as c2, ref_0.s_dist_07 as c3, ref_0.s_dist_10 as c4, ref_0.s_data as c5, ref_0.s_dist_03 as c6, ref_0.s_w_id as c7, ref_0.s_i_id as c8, ref_0.s_ytd as c9, ref_0.s_dist_06 as c10, ref_0.s_dist_08 as c11, ref_0.s_order_cnt as c12, ref_0.s_dist_08 as c13, ref_0.s_dist_08 as c14, ref_0.s_dist_02 as c15, ref_0.s_dist_05 as c16, ref_0.s_dist_07 as c17 from stock as ref_0 where ref_0.s_dist_10 is not NULL ) as subq_0 where subq_0.c12 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 3 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 4 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | 7.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | 6.00 | 100.00 | 100.00 | | +------+-------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 4 rows in set (1.257 sec) -------------- analyze select distinct ref_0.s_dist_04 as c0, ref_0.s_remote_cnt as c1, ref_0.s_dist_09 as c2, ref_0.s_dist_08 as c3, ref_0.s_data as c4, ref_0.s_dist_03 as c5 from stock as ref_0 where ref_0.s_order_cnt is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.532 sec) -------------- analyze select distinct 91 as c0, subq_0.c1 as c1, subq_0.c2 as c2, subq_0.c2 as c3, subq_0.c1 as c4, (select s_dist_07 from stock limit 1 offset 5) as c5, subq_0.c1 as c6, subq_0.c2 as c7, subq_0.c0 as c8, subq_0.c1 as c9, subq_0.c2 as c10 from (select ref_0.s_order_cnt as c0, ref_0.s_dist_09 as c1, ref_0.s_dist_06 as c2 from stock as ref_0 where ref_0.s_data is not NULL) as subq_0 where subq_0.c1 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 2 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | 6.00 | 100.00 | 100.00 | | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.563 sec) -------------- analyze select distinct subq_0.c0 as c0, subq_0.c1 as c1, 1 as c2, subq_0.c0 as c3, (select c_phone from customer limit 1 offset 4) as c4, case when subq_0.c0 is not NULL then subq_0.c0 else subq_0.c0 end as c5, subq_0.c0 as c6, subq_0.c0 as c7, subq_0.c0 as c8, subq_0.c1 as c9 from (select ref_0.s_dist_02 as c0, ref_0.s_i_id as c1 from stock as ref_0 where cast(nullif(ref_0.s_dist_01, ref_0.s_dist_10) as character(24)) is not NULL) as subq_0 where subq_0.c0 is not NULL -------------- +------+-------------+----------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+----------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 2 | SUBQUERY | customer | ALL | NULL | NULL | NULL | NULL | 28980 | 5.00 | 100.00 | 100.00 | | +------+-------------+----------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.627 sec) -------------- analyze select subq_0.c2 as c0, case when subq_0.c6 is not NULL then subq_0.c3 else subq_0.c3 end as c1, (select d_tax from district limit 1 offset 4) as c2 from (select ref_0.ol_d_id as c0, ref_0.ol_i_id as c1, ref_0.ol_i_id as c2, ref_0.ol_dist_info as c3, ref_0.ol_supply_w_id as c4, ref_0.ol_delivery_d as c5, ref_0.ol_quantity as c6 from order_line as ref_0 where EXISTS ( select ref_0.ol_i_id as c0, ref_1.c_phone as c1, ref_1.c_ytd_payment as c2, ref_0.ol_o_id as c3 from customer as ref_1 where ref_0.ol_dist_info is not NULL)) as subq_0 where 29 is not NULL -------------- +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 4 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 28980 | 1.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | district | ALL | NULL | NULL | NULL | NULL | 10 | 5.00 | 100.00 | 100.00 | | +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 3 rows in set (0.642 sec) -------------- analyze select subq_0.c3 as c0, subq_0.c2 as c1, subq_0.c1 as c2, subq_0.c3 as c3, 26 as c4, subq_0.c1 as c5, subq_0.c0 as c6, subq_0.c3 as c7, subq_0.c1 as c8, subq_0.c1 as c9, subq_0.c3 as c10 from (select distinct ref_0.s_dist_04 as c0, ref_0.s_dist_08 as c1, ref_0.s_w_id as c2, ref_0.s_dist_10 as c3 from stock as ref_0 where ref_0.s_w_id is not NULL) as subq_0 where subq_0.c2 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.119 sec) -------------- analyze select distinct 44 as c0, ref_0.i_data as c1, ref_0.i_price as c2, ref_0.i_name as c3, ref_0.i_name as c4, ref_0.i_name as c5, ref_0.i_id as c6, ref_0.i_im_id as c7, ref_0.i_name as c8, 58 as c9 from item as ref_0 where (ref_0.i_data is not NULL) and (ref_0.i_data is not NULL) -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 1 row in set (0.530 sec) -------------- analyze select subq_0.c4 as c0 from (select ref_0.s_quantity as c0, ref_0.s_ytd as c1, (select o_w_id from orders limit 1 offset 5) as c2, ref_1.s_remote_cnt as c3, ref_1.s_dist_03 as c4 from stock as ref_0 left join stock as ref_1 on (ref_0.s_dist_02 is not NULL) where ref_1.s_data is not NULL ) as subq_0 where subq_0.c3 is NULL -------------- +------+-------------+--------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+--------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 3 | SUBQUERY | orders | ALL | NULL | NULL | NULL | NULL | 29406 | NULL | 100.00 | NULL | | +------+-------------+--------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (0.400 sec) -------------- analyze select ref_0.i_im_id as c0, ref_0.i_data as c1, ref_0.i_price as c2 from item as ref_0 where EXISTS ( select ref_1.d_tax as c0, ref_0.i_price as c1, ref_1.d_w_id as c2, ref_0.i_data as c3, ref_1.d_zip as c4, ref_0.i_name as c5, ref_1.d_city as c6, ref_1.d_city as c7, (select s_quantity from stock limit 1 offset 2) as c8, ref_1.d_city as c9, ref_1.d_next_o_id as c10, ref_0.i_im_id as c11, ref_1.d_tax as c12, ref_1.d_street_1 as c13, ref_0.i_data as c14, ref_1.d_name as c15, ref_0.i_id as c16, ref_0.i_im_id as c17, ref_1.d_id as c18, ref_0.i_name as c19, ref_1.d_w_id as c20, ref_1.d_tax as c21, ref_1.d_ytd as c22 from district as ref_1 where EXISTS ( select ref_1.d_state as c0 from orders as ref_2 where ref_1.d_city is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 3 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 4 rows in set (1.126 sec) -------------- analyze select subq_0.c4 as c0, subq_0.c2 as c1, subq_0.c1 as c2, subq_0.c4 as c3, subq_0.c1 as c4, subq_0.c2 as c5 from (select distinct cast(coalesce(ref_0.s_dist_02, ref_0.s_dist_01) as character(24)) as c0, ref_0.s_dist_06 as c1, ref_0.s_dist_03 as c2, ref_0.s_dist_04 as c3, ref_0.s_dist_03 as c4, ref_0.s_dist_08 as c5, ref_0.s_dist_01 as c6, ref_0.s_w_id as c7, ref_0.s_dist_09 as c8 from stock as ref_0 where ref_0.s_dist_01 is not NULL ) as subq_0 where (subq_0.c7 is not NULL) and (subq_0.c6 is not NULL) -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (1.025 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL -------------- +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 7 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 7.00 | 100.00 | 100.00 | | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 5 | SUBQUERY | history | ALL | NULL | NULL | NULL | NULL | 30056 | NULL | 100.00 | NULL | | | 4 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 6 rows in set (1.220 sec) -------------- analyze select (select c_city from customer limit 1 offset 3) as c0 from stock as ref_0 left join stock as ref_1 on (ref_0.s_dist_10 = ref_1.s_dist_01 ) where ((((ref_0.s_dist_03 is not NULL) and (ref_1.s_i_id is NULL)) and (ref_0.s_i_id is not NULL)) and ((EXISTS ( select (select i_id from item limit 1 offset 3) as c0, ref_1.s_dist_07 as c1, ref_2.i_im_id as c2 from item as ref_2 where ref_1.s_dist_08 is not NULL )) or (ref_1.s_w_id is not NULL))) and ((ref_0.s_w_id is NULL) or (ref_1.s_dist_04 is not NULL)) -------------- +------+--------------------+----------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 99438 | NULL | 100.00 | NULL | | | 4 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | NULL | 100.00 | NULL | | | 2 | SUBQUERY | customer | ALL | NULL | NULL | NULL | NULL | 28980 | NULL | 100.00 | NULL | | +------+--------------------+----------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 5 rows in set (0.946 sec) -------------- analyze select distinct ref_0.s_dist_07 as c0, (select s_dist_03 from stock limit 1 offset 93) as c1, ref_0.s_data as c2, ref_0.s_dist_09 as c3, ref_0.s_remote_cnt as c4, case when ref_0.s_i_id is not NULL then ref_0.s_dist_08 else ref_0.s_dist_08 end as c5, ref_0.s_remote_cnt as c6, ref_0.s_dist_01 as c7, ref_0.s_data as c8, ref_0.s_dist_03 as c9, 7 as c10, ref_0.s_dist_02 as c11, ref_0.s_dist_08 as c12 from stock as ref_0 where ref_0.s_quantity is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | | 2 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | 94.00 | 100.00 | 100.00 | | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.932 sec) -------------- analyze select subq_0.c1 as c0, subq_0.c0 as c1, subq_0.c5 as c2, subq_0.c5 as c3, subq_0.c6 as c4, subq_0.c2 as c5, subq_0.c3 as c6, subq_0.c0 as c7, subq_0.c1 as c8, subq_0.c7 as c9, subq_0.c0 as c10, subq_0.c7 as c11, subq_0.c2 as c12, subq_0.c2 as c13, subq_0.c6 as c14, subq_0.c7 as c15 from (select distinct ref_0.i_id as c0, ref_0.i_data as c1, ref_0.i_name as c2, ref_0.i_data as c3, ref_0.i_im_id as c4, ref_0.i_im_id as c5, ref_0.i_im_id as c6, ref_0.i_price as c7 from item as ref_0 where ref_0.i_data is not NULL ) as subq_0 where subq_0.c5 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (0.457 sec) -------------- analyze select cast(nullif(98, 71) as SIGNED) as c0, (select s_dist_08 from stock limit 1 offset 4) as c1 from district as ref_0 where ((ref_0.d_street_2 is NULL) or (((ref_0.d_tax is NULL) and (ref_0.d_ytd is NULL)) and ((ref_0.d_street_2 is NULL) and (ref_0.d_state is NULL)))) or (EXISTS ( select ref_0.d_zip as c0 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_0.d_street_2 as c1, ref_2.h_c_id as c2, ref_1.ol_dist_info as c3, ref_0.d_state as c4, ref_1.ol_dist_info as c5, ref_2.h_c_d_id as c6, ref_2.h_amount as c7, ref_2.h_c_w_id as c8, 87 as c9 from history as ref_2 where (ref_1.ol_i_id is NULL) and (ref_1.ol_i_id is NULL) ))) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 2 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 4 rows in set (1.215 sec) -------------- analyze select 34 as c0, ref_0.no_o_id as c1, ref_1.no_d_id as c2, (select w_ytd from warehouse limit 1 offset 5) as c3, ref_1.no_w_id as c4 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_1.no_o_id is not NULL -------------- +------+-------------+-----------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-----------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | | 2 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | +------+-------------+-----------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ 3 rows in set (2.249 sec) -------------- analyze select subq_0.c0 as c0 from (select distinct ref_0.s_dist_08 as c0, ref_0.s_i_id as c1, ref_0.s_ytd as c2, ref_0.s_w_id as c3, ref_0.s_dist_04 as c4, ref_0.s_quantity as c5, ref_0.s_dist_01 as c6, ref_0.s_data as c7, ref_0.s_dist_07 as c8, ref_0.s_dist_08 as c9, ref_0.s_order_cnt as c10, ref_0.s_dist_07 as c11, 19 as c12, ref_0.s_data as c13, ref_0.s_ytd as c14 from stock as ref_0 where ref_0.s_dist_07 is not NULL) as subq_0 where subq_0.c13 is not NULL -------------- +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 2 | DERIVED | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+------------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------------------+ 2 rows in set (1.164 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL -------------- +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 7 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 7.00 | 100.00 | 100.00 | | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 5 | SUBQUERY | history | ALL | NULL | NULL | NULL | NULL | 30056 | NULL | 100.00 | NULL | | | 4 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 6 rows in set (1.284 sec) -------------- analyze select distinct subq_0.c0 as c0 from (select ref_0.ol_dist_info as c0 from order_line as ref_0 where ref_0.ol_i_id is not NULL) as subq_0 where subq_0.c0 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 1 row in set (0.555 sec) -------------- analyze select cast(nullif(98, 71) as SIGNED) as c0, (select s_dist_08 from stock limit 1 offset 4) as c1 from district as ref_0 where ((ref_0.d_street_2 is NULL) or (((ref_0.d_tax is NULL) and (ref_0.d_ytd is NULL)) and ((ref_0.d_street_2 is NULL) and (ref_0.d_state is NULL)))) or (EXISTS ( select ref_0.d_zip as c0 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_0.d_street_2 as c1, ref_2.h_c_id as c2, ref_1.ol_dist_info as c3, ref_0.d_state as c4, ref_1.ol_dist_info as c5, ref_2.h_c_d_id as c6, ref_2.h_amount as c7, ref_2.h_c_w_id as c8, 87 as c9 from history as ref_2 where (ref_1.ol_i_id is NULL) and (ref_1.ol_i_id is NULL) ))) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 2 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 4 rows in set (1.255 sec) -------------- analyze select ref_0.d_w_id as c0, ref_0.d_next_o_id as c1 from district as ref_0 where EXISTS ( select ref_1.ol_w_id as c0 from order_line as ref_1 where EXISTS ( select ref_2.h_d_id as c0, ref_2.h_data as c1, ref_1.ol_i_id as c2, ref_0.d_street_1 as c3 from history as ref_2 where 18 is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 3 rows in set (1.190 sec) -------------- analyze select subq_0.c2 as c0 from (select ref_1.ol_supply_w_id as c0, 32 as c1, ref_1.ol_amount as c2 from stock as ref_0 left join order_line as ref_1 on (ref_0.s_dist_03 = ref_1.ol_dist_info ) where ref_1.ol_w_id is not NULL ) as subq_0 where subq_0.c2 is NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 2 rows in set (0.964 sec) -------------- analyze select ref_0.d_street_2 as c0, ref_0.d_name as c1, ref_0.d_id as c2, ref_0.d_w_id as c3, ref_0.d_state as c4 from district as ref_0 where EXISTS ( select ref_1.ol_i_id as c0, ref_1.ol_delivery_d as c1, ref_0.d_street_1 as c2, ref_0.d_street_2 as c3, ref_0.d_tax as c4 from order_line as ref_1 where EXISTS ( select ref_3.i_im_id as c0, ref_0.d_zip as c1, ref_2.w_id as c2, ref_0.d_w_id as c3, ref_1.ol_w_id as c4 from warehouse as ref_2 inner join item as ref_3 on (ref_2.w_id = ref_3.i_id ) where ref_2.w_ytd is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 3 rows in set (1.306 sec) -------------- analyze select subq_0.c6 as c0, subq_0.c0 as c1, subq_0.c2 as c2, subq_0.c3 as c3, cast(nullif(cast(nullif(subq_0.c0, subq_0.c3) as character(24)), subq_0.c7) as character(24)) as c4, subq_0.c7 as c5 from (select case when EXISTS ( select ref_0.s_dist_06 as c0, ref_1.h_c_d_id as c1, ref_0.s_dist_06 as c2, 82 as c3, ref_0.s_i_id as c4 from history as ref_1 where EXISTS ( select ref_1.h_c_d_id as c0 from orders as ref_2 where ref_2.o_entry_d is not NULL ) ) then ref_0.s_dist_06 else ref_0.s_dist_06 end as c0, ref_0.s_data as c1, case when (ref_0.s_dist_09 is not NULL) or ((select i_id from item limit 1 offset 5) is not NULL) then ref_0.s_dist_07 else ref_0.s_dist_07 end as c2, ref_0.s_dist_10 as c3, ref_0.s_w_id as c4, ref_0.s_ytd as c5, ref_0.s_dist_10 as c6, ref_0.s_dist_08 as c7, 20 as c8, ref_0.s_dist_06 as c9 from stock as ref_0 where EXISTS ( select 74 as c0, ref_3.w_id as c1, 85 as c2, ref_0.s_dist_02 as c3, ref_0.s_remote_cnt as c4, ref_3.w_street_1 as c5, ref_3.w_street_2 as c6, ref_0.s_dist_08 as c7 from warehouse as ref_3 where 58 is not NULL )) as subq_0 where subq_0.c9 is not NULL -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 6 | DEPENDENT SUBQUERY | ref_3 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | | | 5 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | NULL | 100.00 | NULL | | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 30056 | 1.00 | 100.00 | 100.00 | Using where | | 4 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 29406 | 1.00 | 100.00 | 100.00 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------+ 5 rows in set (0.822 sec) -------------- analyze select ref_0.w_street_1 as c0, ref_0.w_street_2 as c1, ref_0.w_street_2 as c2, ref_0.w_state as c3, ref_0.w_zip as c4 from warehouse as ref_0 where EXISTS ( select 33 as c0 from new_order as ref_1 left join new_order as ref_2 on (ref_1.no_o_id = ref_2.no_w_id ) where ref_2.no_o_id is not NULL ) -------------- +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | | 2 | SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | | | 2 | SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ 3 rows in set (2.400 sec) -------------- analyze select ref_0.no_d_id as c0 from new_order as ref_0 left join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_1.no_w_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ 2 rows in set (2.765 sec) -------------- analyze select ref_0.i_im_id as c0, ref_0.i_data as c1, ref_0.i_price as c2 from item as ref_0 where EXISTS ( select ref_1.d_tax as c0, ref_0.i_price as c1, ref_1.d_w_id as c2, ref_0.i_data as c3, ref_1.d_zip as c4, ref_0.i_name as c5, ref_1.d_city as c6, ref_1.d_city as c7, (select s_quantity from stock limit 1 offset 2) as c8, ref_1.d_city as c9, ref_1.d_next_o_id as c10, ref_0.i_im_id as c11, ref_1.d_tax as c12, ref_1.d_street_1 as c13, ref_0.i_data as c14, ref_1.d_name as c15, ref_0.i_id as c16, ref_0.i_im_id as c17, ref_1.d_id as c18, ref_0.i_name as c19, ref_1.d_w_id as c20, ref_1.d_tax as c21, ref_1.d_ytd as c22 from district as ref_1 where EXISTS ( select ref_1.d_state as c0 from orders as ref_2 where ref_1.d_city is NULL ) ) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 3 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+------------------+ 4 rows in set (0.961 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5, subq_0.c0 as c6, subq_0.c0 as c7, subq_0.c0 as c8, subq_0.c0 as c9, 82 as c10, subq_0.c0 as c11 from (select ref_0.ol_delivery_d as c0 from order_line as ref_0 where EXISTS ( select ref_0.ol_w_id as c0, ref_0.ol_supply_w_id as c1, ref_0.ol_i_id as c2 from new_order as ref_1 where ref_0.ol_amount is not NULL )) as subq_0 where subq_0.c0 is not NULL -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 1.00 | 100.00 | 100.00 | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------+ 2 rows in set (0.458 sec) -------------- analyze select ref_0.no_o_id as c0, ref_1.no_w_id as c1, ref_0.no_d_id as c2, ref_0.no_o_id as c3, ref_1.no_w_id as c4, cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as SIGNED) as c5, ref_1.no_o_id as c6, 36 as c7, ref_1.no_d_id as c8, ref_1.no_w_id as c9 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_0.no_o_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ 2 rows in set (2.831 sec) -------------- analyze select ref_0.d_name as c0 from district as ref_0 left join item as ref_1 on ((ref_0.d_w_id is not NULL) and (EXISTS ( select ref_0.d_w_id as c0, ref_2.no_w_id as c1, ref_0.d_w_id as c2, ref_1.i_id as c3, ref_2.no_d_id as c4, ref_2.no_d_id as c5, ref_2.no_d_id as c6, ref_2.no_w_id as c7, ref_0.d_tax as c8 from new_order as ref_2 where ref_2.no_d_id is not NULL ))) where (ref_0.d_id is not NULL) and (ref_1.i_price is not NULL) -------------- +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | ref_1 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | Using where; Using join buffer (flat, BNL join) | | 2 | DEPENDENT SUBQUERY | ref_2 | ALL | NULL | NULL | NULL | NULL | 7999 | 1.00 | 100.00 | 100.00 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 3 rows in set (1.323 sec) -------------- analyze select distinct ref_0.ol_o_id as c0, ref_0.ol_delivery_d as c1, ref_0.ol_dist_info as c2 from order_line as ref_0 where ref_0.ol_supply_w_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 1 row in set (0.654 sec) -------------- analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL -------------- +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 7 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 7.00 | 100.00 | 100.00 | | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 5 | SUBQUERY | history | ALL | NULL | NULL | NULL | NULL | 30056 | NULL | 100.00 | NULL | | | 4 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | | +------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 6 rows in set (1.261 sec) -------------- analyze select cast(coalesce(subq_0.c1, case when subq_0.c3 is not NULL then subq_0.c0 else subq_0.c0 end ) as character(24)) as c0 from (select ref_0.s_dist_03 as c0, ref_0.s_dist_01 as c1, ref_0.s_data as c2, ref_1.s_dist_01 as c3, ref_1.s_dist_09 as c4 from stock as ref_0 left join stock as ref_1 on (ref_0.s_remote_cnt = ref_1.s_w_id ) where (ref_1.s_order_cnt is not NULL) and (ref_0.s_order_cnt is not NULL) ) as subq_0 where subq_0.c4 is NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+-------+-----------+----------+------------+-------------------------------------------------+ 2 rows in set (0.901 sec) -------------- analyze select subq_0.c3 as c0 from (select ref_1.no_o_id as c0, 48 as c1, ref_1.no_d_id as c2, ref_0.no_d_id as c3, ref_1.no_o_id as c4, ref_0.no_o_id as c5, ref_1.no_w_id as c6, ref_0.no_o_id as c7, ref_1.no_w_id as c8, ref_0.no_o_id as c9, ref_0.no_d_id as c10, ref_0.no_w_id as c11, case when ref_1.no_d_id is not NULL then ref_1.no_w_id else ref_1.no_w_id end as c12 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id ) where ref_1.no_w_id is not NULL ) as subq_0 where 12 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ 2 rows in set (2.813 sec) -------------- analyze select cast(nullif(98, 71) as SIGNED) as c0, (select s_dist_08 from stock limit 1 offset 4) as c1 from district as ref_0 where ((ref_0.d_street_2 is NULL) or (((ref_0.d_tax is NULL) and (ref_0.d_ytd is NULL)) and ((ref_0.d_street_2 is NULL) and (ref_0.d_state is NULL)))) or (EXISTS ( select ref_0.d_zip as c0 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_0.d_street_2 as c1, ref_2.h_c_id as c2, ref_1.ol_dist_info as c3, ref_0.d_state as c4, ref_1.ol_dist_info as c5, ref_2.h_c_d_id as c6, ref_2.h_amount as c7, ref_2.h_c_w_id as c8, 87 as c9 from history as ref_2 where (ref_1.ol_i_id is NULL) and (ref_1.ol_i_id is NULL) ))) -------------- +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 2 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | +------+--------------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 4 rows in set (1.233 sec) -------------- analyze select subq_0.c1 as c0, subq_0.c1 as c1 from (select ref_0.ol_delivery_d as c0, ref_0.ol_w_id as c1, 73 as c2 from order_line as ref_0 where EXISTS ( select ref_0.ol_supply_w_id as c0, ref_0.ol_w_id as c1 from district as ref_1 where EXISTS ( select (select w_zip from warehouse limit 1 offset 5) as c0, ref_0.ol_number as c1, ref_1.d_tax as c2, ref_1.d_w_id as c3, ref_0.ol_amount as c4, ref_0.ol_w_id as c5, ref_0.ol_quantity as c6 from district as ref_2 where (select w_tax from warehouse limit 1 offset 3) is not NULL ) )) as subq_0 where (subq_0.c0 is not NULL) and (subq_0.c1 is not NULL) -------------- +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 4 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 6 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 100.00 | | | 5 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 5 rows in set (1.894 sec) -------------- analyze select subq_0.c1 as c0, subq_0.c1 as c1, subq_0.c0 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c1 as c5, subq_0.c0 as c6 from (select ref_0.i_name as c0, ref_1.ol_d_id as c1 from item as ref_0 left join order_line as ref_1 on (ref_0.i_im_id = ref_1.ol_w_id ) where ref_1.ol_amount is not NULL) as subq_0 where subq_0.c1 is NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 99438 | 100000.00 | 100.00 | 100.00 | | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 2 rows in set (0.929 sec) -------------- analyze select ref_0.no_w_id as c0, cast(nullif(subq_0.c4, ref_0.no_w_id) as SIGNED) as c1, subq_0.c1 as c2, subq_0.c1 as c3, cast(coalesce(case when subq_0.c6 is not NULL then subq_0.c0 else subq_0.c0 end , ref_0.no_o_id) as SIGNED) as c4 from new_order as ref_0 left join (select ref_1.no_w_id as c0, ref_1.no_o_id as c1, 33 as c2, ref_1.no_d_id as c3, ref_1.no_o_id as c4, ref_1.no_w_id as c5, ref_1.no_o_id as c6 from new_order as ref_1 where ref_1.no_o_id is not NULL) as subq_0 on (ref_0.no_o_id = subq_0.c0 ) where subq_0.c0 is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+ 2 rows in set (2.972 sec) -------------- analyze select subq_1.c1 as c0, subq_1.c6 as c1, 5 as c2 from (select ref_2.d_tax as c0, 3 as c1, ref_2.d_id as c2, (select no_w_id from new_order limit 1 offset 2) as c3, subq_0.c10 as c4, subq_0.c15 as c5, subq_0.c15 as c6, subq_0.c6 as c7, (select s_data from stock limit 1 offset 1) as c8, ref_2.d_street_2 as c9, ref_2.d_w_id as c10 from (select ref_0.ol_delivery_d as c0, ref_0.ol_dist_info as c1, ref_0.ol_dist_info as c2, ref_0.ol_quantity as c3, ref_0.ol_number as c4, ref_0.ol_d_id as c5, ref_0.ol_w_id as c6, ref_0.ol_d_id as c7, ref_0.ol_w_id as c8, ref_0.ol_d_id as c9, ref_0.ol_quantity as c10, ref_0.ol_supply_w_id as c11, ref_0.ol_dist_info as c12, ref_0.ol_quantity as c13, ref_0.ol_dist_info as c14, ref_0.ol_supply_w_id as c15 from order_line as ref_0 where EXISTS ( select (select w_zip from warehouse limit 1 offset 5) as c0, ref_1.ol_number as c1, ref_0.ol_delivery_d as c2, ref_0.ol_number as c3, ref_1.ol_supply_w_id as c4, ref_1.ol_i_id as c5, ref_1.ol_amount as c6 from order_line as ref_1 where ref_0.ol_i_id is not NULL ) ) as subq_0 left join district as ref_2 on (subq_0.c15 = ref_2.d_w_id ) where EXISTS ( select ref_2.d_street_1 as c0, ref_3.s_order_cnt as c1, subq_0.c5 as c2, ref_2.d_city as c3, ref_3.s_remote_cnt as c4, subq_0.c13 as c5, subq_0.c6 as c6, subq_0.c13 as c7, subq_0.c15 as c8, ref_2.d_ytd as c9, ref_2.d_city as c10, subq_0.c6 as c11, subq_0.c1 as c12, 86 as c13, ref_2.d_next_o_id as c14, ref_3.s_dist_06 as c15, subq_0.c7 as c16, ref_3.s_dist_02 as c17, subq_0.c3 as c18, subq_0.c11 as c19 from stock as ref_3 where ref_3.s_dist_07 is NULL ) ) as subq_1 where subq_1.c6 is not NULL -------------- +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | ref_2 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 10.00 | Using where; Using join buffer (flat, BNL join) | | 8 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 6 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 1.00 | 100.00 | 100.00 | | | 7 | SUBQUERY | warehouse | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | | | 4 | SUBQUERY | stock | ALL | NULL | NULL | NULL | NULL | 97990 | NULL | 100.00 | NULL | | | 3 | SUBQUERY | new_order | ALL | NULL | NULL | NULL | NULL | 7999 | NULL | 100.00 | NULL | | +------+--------------------+-----------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 7 rows in set (1.084 sec) -------------- analyze select distinct ref_0.ol_number as c0, ref_0.ol_dist_info as c1, ref_0.ol_quantity as c2, ref_0.ol_number as c3, ref_0.ol_i_id as c4, ref_0.ol_d_id as c5 from order_line as ref_0 where ref_0.ol_o_id is not NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where; Using temporary | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+ 1 row in set (1.000 sec) -------------- analyze select ref_0.d_id as c0, ref_0.d_state as c1, ref_0.d_street_1 as c2 from district as ref_0 where EXISTS ( select ref_1.ol_supply_w_id as c0, ref_0.d_city as c1, ref_0.d_street_1 as c2, case when 67 is not NULL then ref_1.ol_amount else ref_1.ol_amount end as c3, ref_0.d_state as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_ytd as c0, (select c_w_id from customer limit 1 offset 1) as c1, ref_1.ol_dist_info as c2, ref_2.ol_w_id as c3, ref_1.ol_amount as c4, ref_1.ol_number as c5, ref_2.ol_number as c6, ref_2.ol_amount as c7, ref_2.ol_d_id as c8 from order_line as ref_2 where ref_0.d_state is NULL )) -------------- +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ | 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where | | 2 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where | | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 4 | SUBQUERY | customer | ALL | NULL | NULL | NULL | NULL | 28980 | NULL | 100.00 | NULL | | +------+--------------------+----------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+ 4 rows in set (1.346 sec) -------------- analyze select subq_0.c2 as c0 from (select ref_1.ol_supply_w_id as c0, 32 as c1, ref_1.ol_amount as c2 from stock as ref_0 left join order_line as ref_1 on (ref_0.s_dist_03 = ref_1.ol_dist_info ) where ref_1.ol_w_id is not NULL ) as subq_0 where subq_0.c2 is NULL -------------- +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ | 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 97990 | 100000.00 | 100.00 | 100.00 | | | 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-------------------------------------------------+ 2 rows in set (0.925 sec)