mysql> create database test; mysql> use test mysql> source test.sql; mysql> explain select count(*) from ndpTest_table22 as t1 left join ndpTest_table22 as t2 on t1.C_BLOB=t2.C_BLOB and t1.C_BLOB in (select t2.C_BLOB from ndpTest_table22 as t2); +----+-------------+-------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+---------------------------------------------+ | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 60 | 100.00 | NULL | | 1 | SIMPLE | t2 | p0,p1,p2 | ref | ndpTest_table22_INDEX3,ndpTest_table22_INDEX5 | ndpTest_table22_INDEX3 | 258 | ndpTest.t1.C_BLOB | 14 | 100.00 | Using where; Start temporary; End temporary | | 1 | SIMPLE | t2 | p0,p1,p2 | ref | ndpTest_table22_INDEX3,ndpTest_table22_INDEX5 | ndpTest_table22_INDEX3 | 258 | ndpTest.t1.C_BLOB | 14 | 100.00 | Using where | +----+-------------+-------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+---------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> select count(*) from ndpTest_table22 as t1 left join ndpTest_table22 as t2 on t1.C_BLOB=t2.C_BLOB and t1.C_BLOB in (select t2.C_BLOB from ndpTest_table22 as t2); +----------+ | count(*) | +----------+ | 106020 | +----------+ 1 row in set (14.07 sec) mysql> select @@optimizer_switch; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@optimizer_switch | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,derived_merge_no_subquery_check=off,gen_col_partition_prune=off,partial_result_cache=on,offset_pushdown=on | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> set optimizer_switch='duplicateweedout=off'; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from ndpTest_table22 as t1 left join ndpTest_table22 as t2 on t1.C_BLOB=t2.C_BLOB and t1.C_BLOB in (select t2.C_BLOB from ndpTest_table22 as t2); +----------+ | count(*) | +----------+ | 2378 | +----------+ 1 row in set (0.34 sec) mysql> explain select count(*) from ndpTest_table22 as t1 left join ndpTest_table22 as t2 on t1.C_BLOB=t2.C_BLOB and t1.C_BLOB in (select t2.C_BLOB from ndpTest_table22 as t2); +----+--------------+-------------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 60 | 100.00 | NULL | | 1 | SIMPLE | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where | | 1 | SIMPLE | t2 | p0,p1,p2 | ref | ndpTest_table22_INDEX3,ndpTest_table22_INDEX5 | ndpTest_table22_INDEX3 | 258 | ndpTest.t1.C_BLOB | 14 | 100.00 | Using where | | 2 | MATERIALIZED | t2 | p0,p1,p2 | ALL | ndpTest_table22_INDEX3,ndpTest_table22_INDEX5 | NULL | NULL | NULL | 60 | 100.00 | NULL | +----+--------------+-------------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+-------------+ 4 rows in set, 1 warning (0.00 sec) mysql> set optimizer_switch='materialization=off'; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from ndpTest_table22 as t1 left join ndpTest_table22 as t2 on t1.C_BLOB=t2.C_BLOB and t1.C_BLOB in (select t2.C_BLOB from ndpTest_table22 as t2); +----+-------------+-------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+-----------------------------+ | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 60 | 100.00 | NULL | | 1 | SIMPLE | t2 | p0,p1,p2 | ref | ndpTest_table22_INDEX3,ndpTest_table22_INDEX5 | ndpTest_table22_INDEX3 | 258 | ndpTest.t1.C_BLOB | 14 | 100.00 | Using where | | 1 | SIMPLE | t2 | p0,p1,p2 | ref | ndpTest_table22_INDEX3,ndpTest_table22_INDEX5 | ndpTest_table22_INDEX3 | 258 | ndpTest.t1.C_BLOB | 14 | 100.00 | Using where; FirstMatch(t2) | +----+-------------+-------+------------+------+-----------------------------------------------+------------------------+---------+-------------------+------+----------+-----------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> select count(*) from ndpTest_table22 as t1 left join ndpTest_table22 as t2 on t1.C_BLOB=t2.C_BLOB and t1.C_BLOB in (select t2.C_BLOB from ndpTest_table22 as t2); +----------+ | count(*) | +----------+ | 2378 | +----------+ 1 row in set (0.64 sec)