mysql> set join_cache_level=default; Query OK, 0 rows affected (0.00 sec) mysql> explain select n_name, ps_partkey, sum(ps_supplycost * ps_availqty) as value1 from partsupp, nation where n_regionkey in (select r_regionkey from region where r_name='ASIA') and (ps_suppkey, n_nationkey) in (select s_suppkey, s_nationkey from supplier where s_acctbal > 5000) group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001/10 from partsupp, nation where n_regionkey in (select r_regionkey from region where r_name='ASIA') and (ps_suppkey, n_nationkey) in (select s_suppkey, s_nationkey from supplier where s_acctbal > 5000) ) order by value1 desc limit 5; +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3x10_myisam.region.r_regionkey | 2 | | | 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | Using where | | 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | | | 4 | SUBQUERY | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where | | 4 | SUBQUERY | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3x10_myisam.region.r_regionkey | 2 | | | 4 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | Using where | | 4 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | | +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ 8 rows in set (0.00 sec) mysql> set join_cache_level=5; Query OK, 0 rows affected (0.00 sec) mysql> explain select n_name, ps_partkey, sum(ps_supplycost * ps_availqty) as value1 from partsupp, nation where n_regionkey in (select r_regionkey from region where r_name='ASIA') and (ps_suppkey, n_nationkey) in (select s_suppkey, s_nationkey from supplier where s_acctbal > 5000) group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001/10 from partsupp, nation where n_regionkey in (select r_regionkey from region where r_name='ASIA') and (ps_suppkey, n_nationkey) in (select s_suppkey, s_nationkey from supplier where s_acctbal > 5000) ) order by value1 desc limit 5; +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3x10_myisam.region.r_regionkey | 2 | Using join buffer | | 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | Using where; Using join buffer | | 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | Using join buffer | | 4 | SUBQUERY | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where | | 4 | SUBQUERY | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3x10_myisam.region.r_regionkey | 2 | Using join buffer | | 4 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | Using where; Using join buffer | | 4 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | Using join buffer | +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ 8 rows in set (0.00 sec) mysql> set join_buffer_size=1024*1024*64; Query OK, 0 rows affected (0.00 sec) mysql> explain select n_name, ps_partkey, sum(ps_supplycost * ps_availqty) as value1 from partsupp, nation where n_regionkey in (select r_regionkey from region where r_name='ASIA') and (ps_suppkey, n_nationkey) in (select s_suppkey, s_nationkey from supplier where s_acctbal > 5000) group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001/10 from partsupp, nation where n_regionkey in (select r_regionkey from region where r_name='ASIA') and (ps_suppkey, n_nationkey) in (select s_suppkey, s_nationkey from supplier where s_acctbal > 5000) ) order by value1 desc limit 5; +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3x10_myisam.region.r_regionkey | 2 | Using join buffer | | 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | Using where; Using join buffer | | 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | Using join buffer | | 4 | SUBQUERY | region | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where | | 4 | SUBQUERY | nation | ref | PRIMARY,i_n_regionkey | i_n_regionkey | 5 | dbt3x10_myisam.region.r_regionkey | 2 | Using join buffer | | 4 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | Using where; Using join buffer | | 4 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | Using join buffer | +----+-------------+----------+------+-----------------------+---------------+---------+-----------------------------------+------+----------------------------------------------+ 8 rows in set (0.01 sec) mysql>