Linux ip-172-26-2-223 5.4.0-1018-aws #18-Ubuntu SMP Wed Jun 24 01:15:00 UTC 2020 x86_64
Apache
: 172.26.2.223 | : 3.145.68.176
Cant Read [ /etc/named.conf ]
8.1.13
www
www.github.com/MadExploits
Terminal
AUTO ROOT
Adminer
Backdoor Destroyer
Linux Exploit
Lock Shell
Lock File
Create User
CREATE RDP
PHP Mailer
BACKCONNECT
UNLOCK SHELL
HASH IDENTIFIER
CPANEL RESET
CREATE WP USER
BLACK DEFEND!
README
+ Create Folder
+ Create File
/
www /
server /
mysql /
mysql-test /
r /
[ HOME SHELL ]
Name
Size
Permission
Action
1st.result
597
B
-rw-r--r--
acl_tables_errors_debug.result
8.93
KB
-rw-r--r--
alias.result
13.58
KB
-rw-r--r--
almost_full.result
1.11
KB
-rw-r--r--
alter_table-big.result
5.45
KB
-rw-r--r--
alter_table.result
154.41
KB
-rw-r--r--
analyze.result
3.69
KB
-rw-r--r--
ansi.result
1.92
KB
-rw-r--r--
archive-big.result
616
B
-rw-r--r--
archive.result
525.18
KB
-rw-r--r--
archive_bitfield.result
4.26
KB
-rw-r--r--
archive_debug.result
355
B
-rw-r--r--
archive_gis.result
25.98
KB
-rw-r--r--
archive_no_symlink.result
882
B
-rw-r--r--
archive_plugin.result
531
B
-rw-r--r--
archive_symlink.result
2.3
KB
-rw-r--r--
auth_rpl.result
1.85
KB
-rw-r--r--
auto_increment.result
12.11
KB
-rw-r--r--
backup.result
4.29
KB
-rw-r--r--
bench_count_distinct.result
557
B
-rw-r--r--
big_test.require
17
B
-rw-r--r--
bigint.result
23.33
KB
-rw-r--r--
binary.result
6.09
KB
-rw-r--r--
binary_to_hex.result
5.87
KB
-rw-r--r--
binlog_tx_isolation.result
1.71
KB
-rw-r--r--
blackhole.result
613
B
-rw-r--r--
blackhole_plugin.result
565
B
-rw-r--r--
bool.result
2.67
KB
-rw-r--r--
bootstrap.result
525
B
-rw-r--r--
bug12368203.result
2.77
KB
-rw-r--r--
bug12427262.result
1.86
KB
-rw-r--r--
bug12969156.result
952
B
-rw-r--r--
bug17076131.result
322
B
-rw-r--r--
bug33509.result
2.31
KB
-rw-r--r--
bug39022.result
1.2
KB
-rw-r--r--
bug46080.result
669
B
-rw-r--r--
bug46261.result
353
B
-rw-r--r--
bug46760.result
1.26
KB
-rw-r--r--
bug47671.result
287
B
-rw-r--r--
bug58669.result
480
B
-rw-r--r--
bulk_replace.result
281
B
-rw-r--r--
cache_innodb.result
6.96
KB
-rw-r--r--
case.result
10.63
KB
-rw-r--r--
case_insensitive_file_system.r...
46
B
-rw-r--r--
case_sensitive_file_system.req...
47
B
-rw-r--r--
cast.result
16.27
KB
-rw-r--r--
change_user.result
2.82
KB
-rw-r--r--
charset.result
2.14
KB
-rw-r--r--
check.result
2.05
KB
-rw-r--r--
check_auto_permission.result
261
B
-rw-r--r--
check_var_limit.require
8
B
-rw-r--r--
client_xml.result
2.38
KB
-rw-r--r--
comment_column.result
141.21
KB
-rw-r--r--
comment_column2.result
355.68
KB
-rw-r--r--
comment_index.result
173.32
KB
-rw-r--r--
comment_table.result
28.95
KB
-rw-r--r--
comments.result
2.34
KB
-rw-r--r--
commit.result
14.83
KB
-rw-r--r--
commit_1innodb.result
19.59
KB
-rw-r--r--
compare.result
5.07
KB
-rw-r--r--
compress.result
66.24
KB
-rw-r--r--
concurrent_innodb_safelog.resu...
32.01
KB
-rw-r--r--
concurrent_innodb_unsafelog.re...
31.74
KB
-rw-r--r--
condition_filter.result
2.17
KB
-rw-r--r--
connect.result
11.02
KB
-rw-r--r--
connect_debug.result
1.46
KB
-rw-r--r--
consistent_snapshot.result
1.14
KB
-rw-r--r--
constraints.result
2.56
KB
-rw-r--r--
count_distinct.result
8.04
KB
-rw-r--r--
count_distinct2.result
2.21
KB
-rw-r--r--
count_distinct3.result
278
B
-rw-r--r--
create-big.result
7.6
KB
-rw-r--r--
create.result
71.42
KB
-rw-r--r--
create_not_windows.result
961
B
-rw-r--r--
create_select_tmp.result
846
B
-rw-r--r--
create_w_max_indexes_64.result
84.46
KB
-rw-r--r--
csv.result
59.08
KB
-rw-r--r--
csv_alter_table.result
1.17
KB
-rw-r--r--
csv_not_null.result
1.93
KB
-rw-r--r--
ctype_ascii.result
2.5
KB
-rw-r--r--
ctype_big5.result
34.28
KB
-rw-r--r--
ctype_binary.result
82.78
KB
-rw-r--r--
ctype_collate.result
18.3
KB
-rw-r--r--
ctype_cp1250_ch.result
22.29
KB
-rw-r--r--
ctype_cp1251.result
96.48
KB
-rw-r--r--
ctype_cp932.result
996
B
-rw-r--r--
ctype_cp932_binlog_row.result
734
B
-rw-r--r--
ctype_cp932_binlog_stm.result
251.36
KB
-rw-r--r--
ctype_create.result
3.12
KB
-rw-r--r--
ctype_errors.result
1.66
KB
-rw-r--r--
ctype_eucjpms.result
400.48
KB
-rw-r--r--
ctype_euckr.result
435.54
KB
-rw-r--r--
ctype_filename.result
324
B
-rw-r--r--
ctype_filesystem.result
341
B
-rw-r--r--
ctype_gb18030.result
69.33
KB
-rw-r--r--
ctype_gb18030_binlog.result
909
B
-rw-r--r--
ctype_gb18030_conversion.resul...
1.17
MB
-rw-r--r--
ctype_gb18030_encoding_cn.resu...
41.39
KB
-rw-r--r--
ctype_gb18030_encoding_utf8.re...
40.64
KB
-rw-r--r--
ctype_gb18030_ligatures.result
24.81
KB
-rw-r--r--
ctype_gb2312.result
33.73
KB
-rw-r--r--
ctype_gbk.result
50.07
KB
-rw-r--r--
ctype_gbk_binlog.result
633
B
-rw-r--r--
ctype_hebrew.result
254
B
-rw-r--r--
ctype_latin1.result
119.29
KB
-rw-r--r--
ctype_latin1_de.result
15.74
KB
-rw-r--r--
ctype_latin2.result
13.71
KB
-rw-r--r--
ctype_latin2_ch.result
25.11
KB
-rw-r--r--
ctype_ldml.result
28.13
KB
-rw-r--r--
ctype_like_range.result
80.89
KB
-rw-r--r--
ctype_many.result
41.87
KB
-rw-r--r--
ctype_mb.result
2.13
KB
-rw-r--r--
ctype_recoding.result
9.6
KB
-rw-r--r--
ctype_sjis.result
195.81
KB
-rw-r--r--
ctype_tis620.result
101.02
KB
-rw-r--r--
ctype_uca.result
155.4
KB
-rw-r--r--
ctype_ucs.result
143.96
KB
-rw-r--r--
ctype_ucs2_def.result
1.05
KB
-rw-r--r--
ctype_ujis.result
365.3
KB
-rw-r--r--
ctype_ujis_ucs2.result
548.6
KB
-rw-r--r--
ctype_utf16.result
47.3
KB
-rw-r--r--
ctype_utf16_def.result
273
B
-rw-r--r--
ctype_utf16_uca.result
72.47
KB
-rw-r--r--
ctype_utf16le.result
52.96
KB
-rw-r--r--
ctype_utf32.result
50.55
KB
-rw-r--r--
ctype_utf32_uca.result
75.29
KB
-rw-r--r--
ctype_utf8.result
169.69
KB
-rw-r--r--
ctype_utf8mb4.result
83.57
KB
-rw-r--r--
ctype_utf8mb4_heap.result
75.01
KB
-rw-r--r--
ctype_utf8mb4_innodb.result
77.85
KB
-rw-r--r--
ctype_utf8mb4_myisam.result
77.43
KB
-rw-r--r--
ctype_utf8mb4_uca.result
38.82
KB
-rw-r--r--
daemonize_opt.result
14
B
-rw-r--r--
datadir_permission.result
1.09
KB
-rw-r--r--
date_formats.result
28.69
KB
-rw-r--r--
ddl_i18n_koi8r.result
123.56
KB
-rw-r--r--
ddl_i18n_utf8.result
126.58
KB
-rw-r--r--
deadlock_innodb.result
2.35
KB
-rw-r--r--
debug_sync.result
12.18
KB
-rw-r--r--
debug_sync2.result
1.15
KB
-rw-r--r--
default.result
8.17
KB
-rw-r--r--
delete.result
22.7
KB
-rw-r--r--
delete_all_rows.result
2.26
KB
-rw-r--r--
deprecate_eof.result
8
KB
-rw-r--r--
deprecated_features.result
2.03
KB
-rw-r--r--
derived.result
172.18
KB
-rw-r--r--
dirty_close.result
345
B
-rw-r--r--
disabled_replication.result
4.82
KB
-rw-r--r--
disabled_storage_engines.resul...
3.28
KB
-rw-r--r--
disconnect_on_expired_password...
1.42
KB
-rw-r--r--
disconnect_on_expired_password...
1.49
KB
-rw-r--r--
distinct.result
80.43
KB
-rw-r--r--
drop-no_root.result
454
B
-rw-r--r--
drop.result
6.71
KB
-rw-r--r--
drop_debug.result
485
B
-rw-r--r--
ds_mrr-big.result
2.93
KB
-rw-r--r--
dynamic_tracing.result
813
B
-rw-r--r--
empty_table.result
229
B
-rw-r--r--
enable_cleartext_plugin.result
1.4
KB
-rw-r--r--
endspace.result
7.25
KB
-rw-r--r--
eq_range_idx_stat.result
6.56
KB
-rw-r--r--
error_simulation.result
3.17
KB
-rw-r--r--
errors.result
6.11
KB
-rw-r--r--
events_1.result
28.3
KB
-rw-r--r--
events_2.result
17.79
KB
-rw-r--r--
events_and_binlog.result
297
B
-rw-r--r--
events_bugs.result
40.94
KB
-rw-r--r--
events_embedded.result
86
B
-rw-r--r--
events_grant.result
10.33
KB
-rw-r--r--
events_logs_tests.result
2.37
KB
-rw-r--r--
events_microsec.result
810
B
-rw-r--r--
events_restart.result
4.36
KB
-rw-r--r--
events_scheduling.result
3.11
KB
-rw-r--r--
events_stress.result
2.39
KB
-rw-r--r--
events_time_zone.result
5.4
KB
-rw-r--r--
events_trans.result
3.61
KB
-rw-r--r--
events_trans_notembedded.resul...
1.88
KB
-rw-r--r--
examined_rows.result
6.73
KB
-rw-r--r--
execution_constants.result
1.03
KB
-rw-r--r--
explain.result
39.39
KB
-rw-r--r--
explain_for_connection_crash.r...
1004
B
-rw-r--r--
explain_for_connection_rqg_jso...
87.48
KB
-rw-r--r--
explain_for_connection_rqg_tra...
35.98
KB
-rw-r--r--
explain_for_connection_small_j...
25.46
KB
-rw-r--r--
explain_for_connection_small_t...
10.3
KB
-rw-r--r--
explain_json_all.result
98.36
KB
-rw-r--r--
explain_json_none.result
102.18
KB
-rw-r--r--
explain_other.result
42.23
KB
-rw-r--r--
file_contents.result
181
B
-rw-r--r--
filesort_debug.result
4.59
KB
-rw-r--r--
filesort_merge.result
2.02
KB
-rw-r--r--
filesort_pack.result
675
B
-rw-r--r--
filter_single_col_idx_big.resu...
38.05
KB
-rw-r--r--
filter_single_col_idx_small.re...
42.99
KB
-rw-r--r--
fix_priv_tables.result
1.96
KB
-rw-r--r--
flush.result
13.78
KB
-rw-r--r--
flush2.result
1.06
KB
-rw-r--r--
flush_block_commit.result
1.47
KB
-rw-r--r--
flush_block_commit_notembedded...
872
B
-rw-r--r--
flush_read_lock.result
73.94
KB
-rw-r--r--
flush_read_lock_kill.result
1.12
KB
-rw-r--r--
flush_table.result
12.33
KB
-rw-r--r--
foreign_key.result
4.84
KB
-rw-r--r--
fulltext.result
32.93
KB
-rw-r--r--
fulltext2.result
7.54
KB
-rw-r--r--
fulltext3.result
815
B
-rw-r--r--
fulltext_cache.result
2.67
KB
-rw-r--r--
fulltext_distinct.result
1.45
KB
-rw-r--r--
fulltext_left_join.result
4
KB
-rw-r--r--
fulltext_multi.result
766
B
-rw-r--r--
fulltext_order_by.result
6.05
KB
-rw-r--r--
fulltext_plugin.result
229
B
-rw-r--r--
fulltext_update.result
1.11
KB
-rw-r--r--
fulltext_var.result
1.3
KB
-rw-r--r--
func_aes.result
12.97
KB
-rw-r--r--
func_aes_cfb1.result
4.5
KB
-rw-r--r--
func_aes_cfb128.result
4.57
KB
-rw-r--r--
func_aes_cfb8.result
4.5
KB
-rw-r--r--
func_aes_kdf_hkdf.result
3.94
KB
-rw-r--r--
func_aes_kdf_pbkdf2_hmac.resul...
6.79
KB
-rw-r--r--
func_aes_misc.result
8.46
KB
-rw-r--r--
func_aes_ofb.result
4.47
KB
-rw-r--r--
func_analyse.result
23.09
KB
-rw-r--r--
func_bitwise_ops.result
80.6
KB
-rw-r--r--
func_compress.result
5.46
KB
-rw-r--r--
func_concat.result
4.37
KB
-rw-r--r--
func_crypt.result
2.05
KB
-rw-r--r--
func_date_add.result
3.74
KB
-rw-r--r--
func_default.result
1.45
KB
-rw-r--r--
func_des_encrypt.result
2.68
KB
-rw-r--r--
func_digest.result
122.35
KB
-rw-r--r--
func_encrypt.result
19.82
KB
-rw-r--r--
func_encrypt_nossl.result
7.75
KB
-rw-r--r--
func_encrypt_ucs2.result
1.21
KB
-rw-r--r--
func_equal.result
1.27
KB
-rw-r--r--
func_found_rows.result
285
B
-rw-r--r--
func_gconcat.result
49.9
KB
-rw-r--r--
func_group.result
66.46
KB
-rw-r--r--
func_group_innodb.result
6.07
KB
-rw-r--r--
func_group_innodb_16k.result
693
B
-rw-r--r--
func_if.result
6.29
KB
-rw-r--r--
func_in_all.result
49.27
KB
-rw-r--r--
func_in_icp.result
48.91
KB
-rw-r--r--
func_in_icp_mrr.result
49.21
KB
-rw-r--r--
func_in_mrr.result
48.93
KB
-rw-r--r--
func_in_mrr_cost.result
48.65
KB
-rw-r--r--
func_in_none.result
48.6
KB
-rw-r--r--
func_isnull.result
827
B
-rw-r--r--
func_like.result
7.78
KB
-rw-r--r--
func_math.result
42.22
KB
-rw-r--r--
func_misc.result
36.8
KB
-rw-r--r--
func_op.result
2.12
KB
-rw-r--r--
func_regexp.result
4.47
KB
-rw-r--r--
func_rollback.result
13.02
KB
-rw-r--r--
func_sapdb.result
12.49
KB
-rw-r--r--
func_set.result
8.9
KB
-rw-r--r--
func_str.result
174.94
KB
-rw-r--r--
func_str_debug.result
349
B
-rw-r--r--
func_str_no_ps.result
1.3
KB
-rw-r--r--
func_system.result
2.9
KB
-rw-r--r--
func_test.result
14.47
KB
-rw-r--r--
func_time.result
71.47
KB
-rw-r--r--
func_timestamp.result
518
B
-rw-r--r--
func_weight_string.result
11.56
KB
-rw-r--r--
function_defaults.result
109.58
KB
-rw-r--r--
gcc296.result
528
B
-rw-r--r--
get_diagnostics.result
28.42
KB
-rw-r--r--
get_table_share.result
20.56
KB
-rw-r--r--
gis-debug.result
10.1
KB
-rw-r--r--
gis-precise.result
162.17
KB
-rw-r--r--
gis-rt-precise.result
2.42
KB
-rw-r--r--
gis-rtree.result
102.08
KB
-rw-r--r--
gis.result
131.8
KB
-rw-r--r--
grant.result
121.17
KB
-rw-r--r--
grant2.result
36.52
KB
-rw-r--r--
grant3.result
8.41
KB
-rw-r--r--
grant4.result
9.36
KB
-rw-r--r--
grant_alter_user.result
40.96
KB
-rw-r--r--
grant_alter_user_qa.result
98.38
KB
-rw-r--r--
grant_cache.result
6.71
KB
-rw-r--r--
grant_debug.result
8.5
KB
-rw-r--r--
grant_explain_non_select.resul...
10.27
KB
-rw-r--r--
grant_lowercase_fs.result
1.2
KB
-rw-r--r--
grant_user_lock.result
16.9
KB
-rw-r--r--
grant_user_lock_qa.result
41.4
KB
-rw-r--r--
greedy_optimizer.result
632.59
KB
-rw-r--r--
greedy_search.result
31.05
KB
-rw-r--r--
group_by.result
120.07
KB
-rw-r--r--
group_by_fd_no_prot.result
81.15
KB
-rw-r--r--
group_by_fd_ps_prot.result
61.28
KB
-rw-r--r--
group_min_max.result
189.52
KB
-rw-r--r--
group_min_max_innodb.result
25.09
KB
-rw-r--r--
gtid_next_xa_binlog_off.result
30.9
KB
-rw-r--r--
gtids_anonymous_trxs_violation...
687
B
-rw-r--r--
handler_innodb.result
41.58
KB
-rw-r--r--
handler_myisam.result
45.48
KB
-rw-r--r--
handler_read_last.result
1.15
KB
-rw-r--r--
have_big5.require
79
B
-rw-r--r--
have_binlog_format_mixed.requi...
40
B
-rw-r--r--
have_binlog_format_row.require
38
B
-rw-r--r--
have_binlog_format_statement.r...
44
B
-rw-r--r--
have_compress.require
38
B
-rw-r--r--
have_cp1250_ch.require
79
B
-rw-r--r--
have_cp1251.require
84
B
-rw-r--r--
have_cp866.require
82
B
-rw-r--r--
have_cp932.require
83
B
-rw-r--r--
have_crypt.require
35
B
-rw-r--r--
have_debug.require
8
B
-rw-r--r--
have_debug_sync.require
13
B
-rw-r--r--
have_eucjpms.require
87
B
-rw-r--r--
have_euckr.require
81
B
-rw-r--r--
have_gb18030.require
87
B
-rw-r--r--
have_gb2312.require
84
B
-rw-r--r--
have_gbk.require
78
B
-rw-r--r--
have_geometry.require
38
B
-rw-r--r--
have_koi8r.require
81
B
-rw-r--r--
have_latin2_ch.require
78
B
-rw-r--r--
have_local_infile.require
36
B
-rw-r--r--
have_log_bin.require
31
B
-rw-r--r--
have_met_timezone.require
43
B
-rw-r--r--
have_moscow_leap_timezone.requ...
46
B
-rw-r--r--
have_mysql_upgrade.result
21
B
-rw-r--r--
have_ndb_debug.require
17
B
-rw-r--r--
have_ndb_extra.require
14
B
-rw-r--r--
have_ndbapi_examples.require
19
B
-rw-r--r--
have_nodebug.require
8
B
-rw-r--r--
have_optimizer_switch.require
19
B
-rw-r--r--
have_outfile.require
55
B
-rw-r--r--
have_partition.require
42
B
-rw-r--r--
have_perror.require
14
B
-rw-r--r--
have_profiling.require
39
B
-rw-r--r--
have_query_cache.require
41
B
-rw-r--r--
have_sjis.require
81
B
-rw-r--r--
have_ssl.require
33
B
-rw-r--r--
have_ssl_is_yes_or_disabled_on...
43
B
-rw-r--r--
have_statement_timeout.require
47
B
-rw-r--r--
have_symlink.require
37
B
-rw-r--r--
have_tis620.require
81
B
-rw-r--r--
have_ucs2.require
80
B
-rw-r--r--
have_ujis.require
81
B
-rw-r--r--
have_utf16.require
82
B
-rw-r--r--
have_utf32.require
82
B
-rw-r--r--
have_utf8.require
80
B
-rw-r--r--
have_utf8mb4.require
86
B
-rw-r--r--
having.result
24.34
KB
-rw-r--r--
heap.result
24.75
KB
-rw-r--r--
heap_auto_increment.result
894
B
-rw-r--r--
heap_btree.result
11.76
KB
-rw-r--r--
heap_hash.result
20.83
KB
-rw-r--r--
help.result
6.64
KB
-rw-r--r--
help_verbose.result
326
B
-rw-r--r--
host_cache_size_functionality....
3.32
KB
-rw-r--r--
ignore_strict.result
30.82
KB
-rw-r--r--
implicit_char_to_num_conversio...
6.44
KB
-rw-r--r--
implicit_commit.result
18.33
KB
-rw-r--r--
import_schema_mismatch.result
478
B
-rw-r--r--
index_merge_delete.result
25.04
KB
-rw-r--r--
index_merge_innodb.result
124.56
KB
-rw-r--r--
index_merge_insert-and-replace...
14.54
KB
-rw-r--r--
index_merge_intersect_dml.resu...
5.85
KB
-rw-r--r--
index_merge_myisam.result
144.52
KB
-rw-r--r--
index_merge_update.result
17.22
KB
-rw-r--r--
information_schema-big.result
2.81
KB
-rw-r--r--
information_schema.result
107.92
KB
-rw-r--r--
information_schema_chmod.resul...
182
B
-rw-r--r--
information_schema_db.result
9.98
KB
-rw-r--r--
information_schema_inno.result
4.33
KB
-rw-r--r--
information_schema_parameters....
22.49
KB
-rw-r--r--
information_schema_part.result
11.97
KB
-rw-r--r--
information_schema_routines.re...
30.81
KB
-rw-r--r--
init_connect.result
3.57
KB
-rw-r--r--
init_file.result
323
B
-rw-r--r--
initialize-bug20350099.result
1.04
KB
-rw-r--r--
initialize-bug20504142.result
1.17
KB
-rw-r--r--
initialize-bug21335821.result
895
B
-rw-r--r--
initialize-errors.result
578
B
-rw-r--r--
initialize-ignore-db.result
742
B
-rw-r--r--
initialize-init-acls.result
1.12
KB
-rw-r--r--
initialize-sha256.result
1.09
KB
-rw-r--r--
initialize.result
3.89
KB
-rw-r--r--
initialize_gtid.result
465
B
-rw-r--r--
innodb_deadlock.result
1.25
KB
-rw-r--r--
innodb_disabled.result
208
B
-rw-r--r--
innodb_explain_json_non_select...
293.06
KB
-rw-r--r--
innodb_explain_json_non_select...
299.29
KB
-rw-r--r--
innodb_explain_non_select_all....
136.52
KB
-rw-r--r--
innodb_explain_non_select_none...
136.37
KB
-rw-r--r--
innodb_icp.result
32.81
KB
-rw-r--r--
innodb_icp_all.result
32.88
KB
-rw-r--r--
innodb_icp_none.result
32.72
KB
-rw-r--r--
innodb_ignore_builtin.result
354
B
-rw-r--r--
innodb_log_file_size_functiona...
2.79
KB
-rw-r--r--
innodb_mrr.result
25.74
KB
-rw-r--r--
innodb_mrr_all.result
25.88
KB
-rw-r--r--
innodb_mrr_cost.result
25.71
KB
-rw-r--r--
innodb_mrr_cost_all.result
25.85
KB
-rw-r--r--
innodb_mrr_cost_icp.result
25.82
KB
-rw-r--r--
innodb_mrr_icp.result
25.85
KB
-rw-r--r--
innodb_mrr_none.result
25.66
KB
-rw-r--r--
innodb_mysql_lock.result
4.05
KB
-rw-r--r--
innodb_mysql_lock2.result
21.4
KB
-rw-r--r--
innodb_mysql_sync.result
25.87
KB
-rw-r--r--
innodb_pk_extension_off.result
20.33
KB
-rw-r--r--
innodb_pk_extension_on.result
20.58
KB
-rw-r--r--
innodb_recovery_with_upper_cas...
1.21
KB
-rw-r--r--
insert.result
30.75
KB
-rw-r--r--
insert_debug.result
1.29
KB
-rw-r--r--
insert_notembedded.result
5.58
KB
-rw-r--r--
insert_select.result
15.34
KB
-rw-r--r--
insert_update.result
18.89
KB
-rw-r--r--
installdb-bad-cipher.result
128
B
-rw-r--r--
internal_tmp_disk_storage_engi...
146.97
KB
-rw-r--r--
ipv4_as_ipv6.result
23.83
KB
-rw-r--r--
ipv6.result
13.89
KB
-rw-r--r--
is_debug_build.require
32
B
-rw-r--r--
is_deprecation.result
558
B
-rw-r--r--
is_embedded.require
16
B
-rw-r--r--
isam.result
15.65
KB
-rw-r--r--
join.result
55.68
KB
-rw-r--r--
join_cache_bka.result
108.89
KB
-rw-r--r--
join_cache_bka_nixbnl.result
108.17
KB
-rw-r--r--
join_cache_bkaunique.result
109.32
KB
-rw-r--r--
join_cache_bnl.result
107.23
KB
-rw-r--r--
join_cache_nojb.result
106.25
KB
-rw-r--r--
join_crash.result
4.53
KB
-rw-r--r--
join_file_handler.result
1.04
KB
-rw-r--r--
join_nested.result
86.66
KB
-rw-r--r--
join_nested_bka.result
92.75
KB
-rw-r--r--
join_nested_bka_nixbnl.result
89.71
KB
-rw-r--r--
join_optimizer.result
2.54
KB
-rw-r--r--
join_outer.result
96.52
KB
-rw-r--r--
join_outer_bka.result
96.89
KB
-rw-r--r--
join_outer_bka_nixbnl.result
94.69
KB
-rw-r--r--
join_outer_innodb.result
3.27
KB
-rw-r--r--
key.result
22.58
KB
-rw-r--r--
key_cache.result
11.88
KB
-rw-r--r--
key_diff.result
1.15
KB
-rw-r--r--
key_primary.result
890
B
-rw-r--r--
keywords.result
6.63
KB
-rw-r--r--
kill.result
8.88
KB
-rw-r--r--
kill_debug.result
1.58
KB
-rw-r--r--
limit.result
8.31
KB
-rw-r--r--
loaddata.result
20.8
KB
-rw-r--r--
loaddata_autocom_innodb.result
651
B
-rw-r--r--
loadxml.result
2.7
KB
-rw-r--r--
locale.result
5.59
KB
-rw-r--r--
lock.result
14.09
KB
-rw-r--r--
lock_multi.result
63.2
KB
-rw-r--r--
lock_multi_bug38499.result
964
B
-rw-r--r--
lock_multi_bug38691.result
812
B
-rw-r--r--
lock_sync.result
27.44
KB
-rw-r--r--
lock_tables_lost_commit.result
150
B
-rw-r--r--
locking_service.result
31.09
KB
-rw-r--r--
log_errchk.result
663
B
-rw-r--r--
log_state.result
9.66
KB
-rw-r--r--
log_state_bug33693.result
111
B
-rw-r--r--
log_tables-big.result
996
B
-rw-r--r--
log_tables.result
41.81
KB
-rw-r--r--
log_tables_debug.result
770
B
-rw-r--r--
log_tables_upgrade.result
2.35
KB
-rw-r--r--
log_timestamps.result
71
B
-rw-r--r--
long_tmpdir.result
99
B
-rw-r--r--
lowercase0.require
45
B
-rw-r--r--
lowercase1.require
45
B
-rw-r--r--
lowercase2.require
45
B
-rw-r--r--
lowercase_fs_off.result
3.46
KB
-rw-r--r--
lowercase_fs_on.result
75
B
-rw-r--r--
lowercase_mixed_tmpdir.result
162
B
-rw-r--r--
lowercase_mixed_tmpdir_innodb....
190
B
-rw-r--r--
lowercase_table.result
3.24
KB
-rw-r--r--
lowercase_table2.result
8.66
KB
-rw-r--r--
lowercase_table4.result
4.63
KB
-rw-r--r--
lowercase_table5.result
673
B
-rw-r--r--
lowercase_table_grant.result
2.01
KB
-rw-r--r--
lowercase_table_qcache.result
847
B
-rw-r--r--
lowercase_utf8.result
173
B
-rw-r--r--
lowercase_view.result
10.21
KB
-rw-r--r--
m_i_db.result
15.5
KB
-rw-r--r--
max_statement_time.result
12.81
KB
-rw-r--r--
mdl_sync.result
113.92
KB
-rw-r--r--
mdl_tablespace.result
34.72
KB
-rw-r--r--
merge-big.result
728
B
-rw-r--r--
merge.result
100.14
KB
-rw-r--r--
merge_innodb.result
1.31
KB
-rw-r--r--
merge_mmap.result
3.33
KB
-rw-r--r--
metadata.result
17.03
KB
-rw-r--r--
mix2_myisam.result
66.36
KB
-rw-r--r--
mix2_myisam_ucs2.result
15.35
KB
-rw-r--r--
multi_plugin_load.result
347
B
-rw-r--r--
multi_plugin_load_add.result
455
B
-rw-r--r--
multi_plugin_load_add2.result
455
B
-rw-r--r--
multi_statement.result
563
B
-rw-r--r--
multi_update.result
26.47
KB
-rw-r--r--
multi_update2.result
1016
B
-rw-r--r--
multi_update_innodb.result
4.52
KB
-rw-r--r--
multi_update_tiny_hash.result
1.5
KB
-rw-r--r--
myisam-blob.result
1.41
KB
-rw-r--r--
myisam-system.result
394
B
-rw-r--r--
myisam.result
96.84
KB
-rw-r--r--
myisam_crash_before_flush_keys...
1.15
KB
-rw-r--r--
myisam_debug.result
1.21
KB
-rw-r--r--
myisam_explain_json_non_select...
287.97
KB
-rw-r--r--
myisam_explain_json_non_select...
294.08
KB
-rw-r--r--
myisam_explain_non_select_all....
133.64
KB
-rw-r--r--
myisam_explain_non_select_none...
133.36
KB
-rw-r--r--
myisam_icp.result
31.96
KB
-rw-r--r--
myisam_icp_all.result
32.02
KB
-rw-r--r--
myisam_icp_none.result
31.84
KB
-rw-r--r--
myisam_mrr.result
24.26
KB
-rw-r--r--
myisam_mrr_all.result
24.43
KB
-rw-r--r--
myisam_mrr_cost.result
24.21
KB
-rw-r--r--
myisam_mrr_cost_all.result
24.38
KB
-rw-r--r--
myisam_mrr_cost_icp.result
24.35
KB
-rw-r--r--
myisam_mrr_icp.result
24.4
KB
-rw-r--r--
myisam_mrr_none.result
24.16
KB
-rw-r--r--
myisam_recover.result
4.57
KB
-rw-r--r--
myisam_row_rpl.result
2.04
KB
-rw-r--r--
myisampack.result
7.56
KB
-rw-r--r--
mysql-bug41486.result
465
B
-rw-r--r--
mysql-bug45236.result
329
B
-rw-r--r--
mysql.result
9.77
KB
-rw-r--r--
mysql_batch_mode.result
84
B
-rw-r--r--
mysql_binary_mode.result
1.52
KB
-rw-r--r--
mysql_client_test.result
8.92
KB
-rw-r--r--
mysql_client_test_embedded.res...
111
B
-rw-r--r--
mysql_client_test_qcache.resul...
500
B
-rw-r--r--
mysql_comments.result
6.68
KB
-rw-r--r--
mysql_config_editor.result
8.01
KB
-rw-r--r--
mysql_cp932.result
37
B
-rw-r--r--
mysql_embedded.result
181
B
-rw-r--r--
mysql_embedded_client_test.res...
153
B
-rw-r--r--
mysql_locale_posix.result
973
B
-rw-r--r--
mysql_not_windows.result
477
B
-rw-r--r--
mysql_plugin.result
4.92
KB
-rw-r--r--
mysql_protocols.result
208
B
-rw-r--r--
mysql_ssl.result
152
B
-rw-r--r--
mysql_ssl_default.result
889
B
-rw-r--r--
mysql_tzinfo_to_sql.result
22.11
KB
-rw-r--r--
mysql_tzinfo_to_sql_sys.result
1.28
KB
-rw-r--r--
mysql_upgrade.result
136.32
KB
-rw-r--r--
mysql_upgrade_slave_master_inf...
5
KB
-rw-r--r--
mysql_upgrade_ssl.result
5.65
KB
-rw-r--r--
mysql_upgrade_with_session_use...
2.77
KB
-rw-r--r--
mysqladmin.result
1.22
KB
-rw-r--r--
mysqladmin_shutdown.result
170
B
-rw-r--r--
mysqlbinlog.result
31.28
KB
-rw-r--r--
mysqlbinlog_base64_flag.result
3.89
KB
-rw-r--r--
mysqlbinlog_debug.result
235
B
-rw-r--r--
mysqlbinlog_hexdump.result
6.5
KB
-rw-r--r--
mysqlbinlog_mixed_or_statment....
855
B
-rw-r--r--
mysqlbinlog_raw_mode.result
8.32
KB
-rw-r--r--
mysqlbinlog_raw_mode_win.resul...
8.08
KB
-rw-r--r--
mysqlbinlog_row_big.result
2.42
KB
-rw-r--r--
mysqlcheck.result
19.17
KB
-rw-r--r--
mysqld--defaults-file.result
908
B
-rw-r--r--
mysqld--help-notwin.result
67.99
KB
-rw-r--r--
mysqld--help-win.result
68.06
KB
-rw-r--r--
mysqld_daemon.result
66
B
-rw-r--r--
mysqld_safe.result
142
B
-rw-r--r--
mysqldump-binary.result
3.51
KB
-rw-r--r--
mysqldump-compat.result
111
B
-rw-r--r--
mysqldump-max.result
11.33
KB
-rw-r--r--
mysqldump-no-binlog.result
50
B
-rw-r--r--
mysqldump.result
295.21
KB
-rw-r--r--
mysqldump_bug29998457.result
3.47
KB
-rw-r--r--
mysqldump_bug30042589.result
3.05
KB
-rw-r--r--
mysqldump_bugs.result
5.04
KB
-rw-r--r--
mysqldump_gtid_state.result
2.69
KB
-rw-r--r--
mysqldump_restore.result
4.01
KB
-rw-r--r--
mysqldumpslow.result
1.13
KB
-rw-r--r--
mysqlimport.result
833
B
-rw-r--r--
mysqlpump.result
16.38
KB
-rw-r--r--
mysqlpump_basic.result
23.91
KB
-rw-r--r--
mysqlpump_bugs.result
850
B
-rw-r--r--
mysqlpump_charset.result
15.54
KB
-rw-r--r--
mysqlpump_concurrency.result
11.41
KB
-rw-r--r--
mysqlpump_extended.result
13.01
KB
-rw-r--r--
mysqlpump_filters.result
11.34
KB
-rw-r--r--
mysqlpump_multi_thread.result
11.53
KB
-rw-r--r--
mysqlpump_partial_bkp.result
797
B
-rw-r--r--
mysqlshow.result
9.96
KB
-rw-r--r--
mysqlslap.result
7.53
KB
-rw-r--r--
mysqltest.result
33.29
KB
-rw-r--r--
named_pipe.result
65.98
KB
-rw-r--r--
negation_elimination.result
13
KB
-rw-r--r--
no-threads.result
329
B
-rw-r--r--
no_binlog.result
64
B
-rw-r--r--
no_binlog_gtid_empty_statement...
675
B
-rw-r--r--
no_binlog_gtid_empty_transacti...
683
B
-rw-r--r--
no_binlog_gtid_mode_on.result
4.75
KB
-rw-r--r--
no_binlog_gtid_mode_on_explici...
3.84
KB
-rw-r--r--
no_binlog_gtid_next_begin_caus...
1.4
KB
-rw-r--r--
no_binlog_gtid_next_partially_...
11.91
KB
-rw-r--r--
no_binlog_gtid_next_partially_...
1.13
KB
-rw-r--r--
no_binlog_gtid_next_partially_...
2.3
KB
-rw-r--r--
no_binlog_gtid_next_single_stm...
9.6
KB
-rw-r--r--
no_binlog_gtid_next_temporary_...
1.1
KB
-rw-r--r--
no_engine_substitution.result
6.01
KB
-rw-r--r--
not_asan.require
7
B
-rw-r--r--
not_embedded.require
16
B
-rw-r--r--
not_embedded_server.result
5.86
KB
-rw-r--r--
not_ndb.require
39
B
-rw-r--r--
not_openssl.require
36
B
-rw-r--r--
not_partition.result
5.75
KB
-rw-r--r--
not_ssl.require
32
B
-rw-r--r--
not_true.require
10
B
-rw-r--r--
not_valgrind.require
17
B
-rw-r--r--
not_windows.require
7
B
-rw-r--r--
null.result
16.17
KB
-rw-r--r--
null_key_all.result
27.81
KB
-rw-r--r--
null_key_icp.result
27.74
KB
-rw-r--r--
null_key_none.result
27.68
KB
-rw-r--r--
odbc.result
1.41
KB
-rw-r--r--
olap.result
20.44
KB
-rw-r--r--
one_thread_per_connection.requ...
44
B
-rw-r--r--
openssl.require
37
B
-rw-r--r--
openssl_1.result
9.65
KB
-rw-r--r--
opt_costmodel.result
11.47
KB
-rw-r--r--
opt_costmodel_downgrade.result
2.55
KB
-rw-r--r--
opt_costmodel_flush.result
27.03
KB
-rw-r--r--
opt_costmodel_pfs.result
296
B
-rw-r--r--
opt_costmodel_restart.result
7.52
KB
-rw-r--r--
opt_costmodel_tables.result
5.59
KB
-rw-r--r--
opt_costmodel_warnings.result
2.52
KB
-rw-r--r--
opt_hint_timeout.result
3.21
KB
-rw-r--r--
opt_hints.result
110.61
KB
-rw-r--r--
opt_hints_lowercase.result
1.95
KB
-rw-r--r--
opt_hints_pfs.result
1.93
KB
-rw-r--r--
opt_hints_subquery.result
135.37
KB
-rw-r--r--
optimizer_bug12837084.result
13.97
KB
-rw-r--r--
optimizer_debug_sync.result
1.32
KB
-rw-r--r--
optimizer_switch.result
30.14
KB
-rw-r--r--
order_by_all.result
78.98
KB
-rw-r--r--
order_by_icp_mrr.result
78.93
KB
-rw-r--r--
order_by_limit.result
18.46
KB
-rw-r--r--
order_by_none.result
78.59
KB
-rw-r--r--
order_by_sortkey.result
3.19
KB
-rw-r--r--
order_fill_sortbuf.result
262
B
-rw-r--r--
outfile.result
2.2
KB
-rw-r--r--
outfile_loaddata.result
11.02
KB
-rw-r--r--
overflow.result
235
B
-rw-r--r--
packet.result
9.48
KB
-rw-r--r--
parser-big-32bit.result
1.35
KB
-rw-r--r--
parser-big-64bit.result
1.35
KB
-rw-r--r--
parser.result
60.1
KB
-rw-r--r--
parser_bug21114_innodb.result
25.39
KB
-rw-r--r--
parser_not_embedded.result
4.42
KB
-rw-r--r--
parser_precedence.result
23.96
KB
-rw-r--r--
parser_stack.result
9.54
KB
-rw-r--r--
partition.result
124.74
KB
-rw-r--r--
partition_archive.result
7.74
KB
-rw-r--r--
partition_binlog.result
2.41
KB
-rw-r--r--
partition_binlog_stmt.result
955
B
-rw-r--r--
partition_blackhole.result
531
B
-rw-r--r--
partition_bug18198.result
8.07
KB
-rw-r--r--
partition_cache.result
7.16
KB
-rw-r--r--
partition_charset.result
434
B
-rw-r--r--
partition_column.result
25.2
KB
-rw-r--r--
partition_column_prune.result
4.39
KB
-rw-r--r--
partition_csv.result
2.69
KB
-rw-r--r--
partition_datatype.result
47.14
KB
-rw-r--r--
partition_debug.result
985
B
-rw-r--r--
partition_debug_sync.result
6.26
KB
-rw-r--r--
partition_debug_sync_stmt.resu...
3.65
KB
-rw-r--r--
partition_deprecation.result
4.88
KB
-rw-r--r--
partition_error.result
84.03
KB
-rw-r--r--
partition_exchange.result
65.43
KB
-rw-r--r--
partition_explicit_prune.resul...
57.97
KB
-rw-r--r--
partition_federated.result
271
B
-rw-r--r--
partition_grant.result
1.67
KB
-rw-r--r--
partition_hash.result
11.15
KB
-rw-r--r--
partition_index_innodb.result
6.16
KB
-rw-r--r--
partition_index_myisam.result
5.35
KB
-rw-r--r--
partition_innodb.result
50.73
KB
-rw-r--r--
partition_innodb_plugin.result
3.11
KB
-rw-r--r--
partition_innodb_semi_consiste...
2.92
KB
-rw-r--r--
partition_innodb_stmt.result
1.12
KB
-rw-r--r--
partition_innodb_tablespace.re...
35.12
KB
-rw-r--r--
partition_key_cache.result
22.78
KB
-rw-r--r--
partition_list.result
13.93
KB
-rw-r--r--
partition_locking.result
246.59
KB
-rw-r--r--
partition_locking_4.result
1.7
KB
-rw-r--r--
partition_mgm.result
9.83
KB
-rw-r--r--
partition_mgm_err.result
7.48
KB
-rw-r--r--
partition_mgm_err2.result
373
B
-rw-r--r--
partition_myisam.result
10.25
KB
-rw-r--r--
partition_not_blackhole.result
456
B
-rw-r--r--
partition_not_windows.result
4.27
KB
-rw-r--r--
partition_order.result
16.29
KB
-rw-r--r--
partition_plugin.result
4.48
KB
-rw-r--r--
partition_pruning.result
290.58
KB
-rw-r--r--
partition_range.result
46.69
KB
-rw-r--r--
partition_rename_longfilename....
1.91
KB
-rw-r--r--
partition_symlink.result
6.25
KB
-rw-r--r--
partition_sync.result
1.35
KB
-rw-r--r--
partition_truncate.result
675
B
-rw-r--r--
partition_utf8.result
2.01
KB
-rw-r--r--
partition_windows.result
3.3
KB
-rw-r--r--
per_thread_connection_handler....
1.45
KB
-rw-r--r--
perror-win.result
453
B
-rw-r--r--
perror.result
471
B
-rw-r--r--
plugin.result
8.52
KB
-rw-r--r--
plugin_auth.result
33.72
KB
-rw-r--r--
plugin_auth_expire.result
644
B
-rw-r--r--
plugin_auth_qa.result
24.47
KB
-rw-r--r--
plugin_auth_qa_1.result
16.11
KB
-rw-r--r--
plugin_auth_qa_2.result
11.17
KB
-rw-r--r--
plugin_auth_qa_3.result
1.29
KB
-rw-r--r--
plugin_auth_sha256.result
5.52
KB
-rw-r--r--
plugin_auth_sha256_2.result
1.96
KB
-rw-r--r--
plugin_auth_sha256_server_defa...
3.7
KB
-rw-r--r--
plugin_auth_sha256_server_defa...
4.02
KB
-rw-r--r--
plugin_auth_sha256_tls.result
2.53
KB
-rw-r--r--
plugin_auth_user_lock.result
1
KB
-rw-r--r--
plugin_load.result
76
B
-rw-r--r--
plugin_load_option.result
321
B
-rw-r--r--
plugin_not_embedded.result
995
B
-rw-r--r--
preload.result
3.84
KB
-rw-r--r--
profiling.result
16.4
KB
-rw-r--r--
ps.result
125.1
KB
-rw-r--r--
ps_10nestset.result
2.08
KB
-rw-r--r--
ps_11bugs.result
6.01
KB
-rw-r--r--
ps_1general.result
35.8
KB
-rw-r--r--
ps_2myisam.result
103.52
KB
-rw-r--r--
ps_3innodb.result
103.06
KB
-rw-r--r--
ps_4heap.result
102.98
KB
-rw-r--r--
ps_5merge.result
201.74
KB
-rw-r--r--
ps_ddl.result
53.74
KB
-rw-r--r--
ps_ddl1.result
11.06
KB
-rw-r--r--
ps_grant.result
3.99
KB
-rw-r--r--
ps_not_windows.result
516
B
-rw-r--r--
ps_w_max_indexes_64.result
189.58
KB
-rw-r--r--
python_with_json.require
27
B
-rw-r--r--
query_cache.result
89.5
KB
-rw-r--r--
query_cache_28249.result
3.58
KB
-rw-r--r--
query_cache_debug.result
11.1
KB
-rw-r--r--
query_cache_disabled.result
1.96
KB
-rw-r--r--
query_cache_merge.result
32.8
KB
-rw-r--r--
query_cache_notembedded.result
11.37
KB
-rw-r--r--
query_cache_ps_no_prot.result
12.26
KB
-rw-r--r--
query_cache_ps_ps_prot.result
11.09
KB
-rw-r--r--
query_cache_size_functionality...
7.69
KB
-rw-r--r--
query_cache_type_functionality...
9.19
KB
-rw-r--r--
query_cache_with_views.result
6.02
KB
-rw-r--r--
range_all.result
145.86
KB
-rw-r--r--
range_icp.result
145.41
KB
-rw-r--r--
range_icp_mrr.result
145.8
KB
-rw-r--r--
range_mrr.result
145.32
KB
-rw-r--r--
range_mrr_cost.result
144.95
KB
-rw-r--r--
range_none.result
144.9
KB
-rw-r--r--
range_with_memory_limit.result
190.38
KB
-rw-r--r--
read_many_rows_innodb.result
2.83
KB
-rw-r--r--
read_only.result
23.09
KB
-rw-r--r--
read_only_innodb.result
5.42
KB
-rw-r--r--
relay_log_index_variables.resu...
469
B
-rw-r--r--
relay_log_variables.result
475
B
-rw-r--r--
rename.result
1.58
KB
-rw-r--r--
renamedb.result
880
B
-rw-r--r--
repair.result
6.43
KB
-rw-r--r--
replace.result
1.27
KB
-rw-r--r--
reset_connection.result
6.46
KB
-rw-r--r--
rewrite_general_log.result
6.48
KB
-rw-r--r--
rewrite_slow_log.result
3.75
KB
-rw-r--r--
rollback.result
574
B
-rw-r--r--
round.result
6.92
KB
-rw-r--r--
row.result
23.11
KB
-rw-r--r--
rowid_order_innodb.result
3.6
KB
-rw-r--r--
rpl_colSize.result
4.39
KB
-rw-r--r--
rpl_connect_attr.result
1.21
KB
-rw-r--r--
rpl_extraColmaster_innodb.resu...
23.85
KB
-rw-r--r--
rpl_extraColmaster_myisam.resu...
23.85
KB
-rw-r--r--
rpl_multi_source_mysqldump_sla...
1.5
KB
-rw-r--r--
rpl_mysqldump_slave.result
867
B
-rw-r--r--
schema.result
2.34
KB
-rw-r--r--
secure_file_priv_win.result
1.43
KB
-rw-r--r--
select_all.result
180.1
KB
-rw-r--r--
select_all_bka.result
180.83
KB
-rw-r--r--
select_all_bka_nixbnl.result
180.1
KB
-rw-r--r--
select_for_update.result
755
B
-rw-r--r--
select_found.result
6.96
KB
-rw-r--r--
select_icp_mrr.result
180.04
KB
-rw-r--r--
select_icp_mrr_bka.result
180.77
KB
-rw-r--r--
select_icp_mrr_bka_nixbnl.resu...
180.04
KB
-rw-r--r--
select_none.result
179.38
KB
-rw-r--r--
select_none_bka.result
179.47
KB
-rw-r--r--
select_none_bka_nixbnl.result
178.64
KB
-rw-r--r--
select_safe.result
9.97
KB
-rw-r--r--
server_id.require
32
B
-rw-r--r--
server_id1.require
34
B
-rw-r--r--
server_offline_1.result
2.03
KB
-rw-r--r--
server_offline_2.result
1.28
KB
-rw-r--r--
server_offline_3.result
1.31
KB
-rw-r--r--
server_offline_4.result
958
B
-rw-r--r--
server_offline_5.result
7.23
KB
-rw-r--r--
server_offline_6.result
1.2
KB
-rw-r--r--
server_uuid.result
785
B
-rw-r--r--
server_uuid_embedded.result
325
B
-rw-r--r--
session_tracker.result
23.61
KB
-rw-r--r--
session_tracker_trx_state.resu...
38.38
KB
-rw-r--r--
shm.result
66.48
KB
-rw-r--r--
shm_server_restart.result
20
B
-rw-r--r--
show_check.result
78.63
KB
-rw-r--r--
show_processlist_state.result
150
B
-rw-r--r--
show_profile.result
491
B
-rw-r--r--
show_variables.result
372
B
-rw-r--r--
shutdown.result
325
B
-rw-r--r--
signal.result
71.01
KB
-rw-r--r--
signal_code.result
1020
B
-rw-r--r--
signal_demo1.result
6.25
KB
-rw-r--r--
signal_demo2.result
3.86
KB
-rw-r--r--
signal_demo3.result
3.33
KB
-rw-r--r--
signal_sqlmode.result
2.63
KB
-rw-r--r--
single_delete_update.result
24.5
KB
-rw-r--r--
skip_grants.result
2.7
KB
-rw-r--r--
skip_grants_flush.result
352
B
-rw-r--r--
skip_log_bin.result
167
B
-rw-r--r--
skip_name_resolve.result
2.51
KB
-rw-r--r--
slave-running.result
71
B
-rw-r--r--
slave-stopped.result
72
B
-rw-r--r--
slow_log.result
2.57
KB
-rw-r--r--
sort_buffer_size_functionality...
6.61
KB
-rw-r--r--
sp-big.result
1.47
KB
-rw-r--r--
sp-bugs.result
11.46
KB
-rw-r--r--
sp-code.result
29.86
KB
-rw-r--r--
sp-destruct.result
7.02
KB
-rw-r--r--
sp-dynamic.result
9.73
KB
-rw-r--r--
sp-error.result
84.05
KB
-rw-r--r--
sp-fib.result
656
B
-rw-r--r--
sp-lock.result
21.56
KB
-rw-r--r--
sp-no-code.result
307
B
-rw-r--r--
sp-prelocking.result
7
KB
-rw-r--r--
sp-security.result
26.42
KB
-rw-r--r--
sp-threads.result
2.01
KB
-rw-r--r--
sp-ucs2.result
5.45
KB
-rw-r--r--
sp-vars.result
30.01
KB
-rw-r--r--
sp.result
192.98
KB
-rw-r--r--
sp_debug.result
735
B
-rw-r--r--
sp_gis.result
853
B
-rw-r--r--
sp_notembedded.result
14.61
KB
-rw-r--r--
sp_stress_case.result
2.53
KB
-rw-r--r--
sp_sync.result
3.17
KB
-rw-r--r--
sp_trans.result
14.83
KB
-rw-r--r--
sp_trans_log.result
891
B
-rw-r--r--
sp_validation.result
34.85
KB
-rw-r--r--
sql_mode.result
23.42
KB
-rw-r--r--
sql_mode_default.result
3.39
KB
-rw-r--r--
ssl-big.result
69
B
-rw-r--r--
ssl-crl-revoked-crl.result
76
B
-rw-r--r--
ssl-sha512.result
636
B
-rw-r--r--
ssl.result
66.63
KB
-rw-r--r--
ssl_8k_key.result
42
B
-rw-r--r--
ssl_and_innodb.result
306
B
-rw-r--r--
ssl_auto_detect_openssl.result
361
B
-rw-r--r--
ssl_ca.result
857
B
-rw-r--r--
ssl_cipher.result
295
B
-rw-r--r--
ssl_compress.result
66.25
KB
-rw-r--r--
ssl_connect.result
10
B
-rw-r--r--
ssl_crl.result
1.21
KB
-rw-r--r--
ssl_crl_clients-valid.result
1.01
KB
-rw-r--r--
ssl_crl_clients.result
427
B
-rw-r--r--
ssl_crl_clients_valid.result
673
B
-rw-r--r--
ssl_crl_crlpath.result
808
B
-rw-r--r--
ssl_deprecated_tls_versions.re...
334
B
-rw-r--r--
ssl_verify_identity.result
1.35
KB
-rw-r--r--
status.result
11.15
KB
-rw-r--r--
status2.result
1.35
KB
-rw-r--r--
status_bug17954.result
582
B
-rw-r--r--
status_debug.result
3.48
KB
-rw-r--r--
strict.result
78.07
KB
-rw-r--r--
strict_autoinc_1myisam.result
1.05
KB
-rw-r--r--
strict_autoinc_2innodb.result
1.05
KB
-rw-r--r--
strict_autoinc_3heap.result
1.05
KB
-rw-r--r--
subquery_all.result
298.58
KB
-rw-r--r--
subquery_all_bka.result
298.82
KB
-rw-r--r--
subquery_all_bka_nixbnl.result
298.37
KB
-rw-r--r--
subquery_bugs.result
4.36
KB
-rw-r--r--
subquery_mat.result
133.51
KB
-rw-r--r--
subquery_mat_all.result
123.66
KB
-rw-r--r--
subquery_mat_none.result
122.3
KB
-rw-r--r--
subquery_nomat_nosj.result
297.96
KB
-rw-r--r--
subquery_nomat_nosj_bka.result
298.16
KB
-rw-r--r--
subquery_nomat_nosj_bka_nixbnl...
297.81
KB
-rw-r--r--
subquery_none.result
297.84
KB
-rw-r--r--
subquery_none_bka.result
297.94
KB
-rw-r--r--
subquery_none_bka_nixbnl.resul...
297.58
KB
-rw-r--r--
subquery_sj_all.result
500.49
KB
-rw-r--r--
subquery_sj_all_bka.result
502.11
KB
-rw-r--r--
subquery_sj_all_bka_nixbnl.res...
490.45
KB
-rw-r--r--
subquery_sj_all_bkaunique.resu...
498.68
KB
-rw-r--r--
subquery_sj_dupsweed.result
502.87
KB
-rw-r--r--
subquery_sj_dupsweed_bka.resul...
502.97
KB
-rw-r--r--
subquery_sj_dupsweed_bka_nixbn...
484.75
KB
-rw-r--r--
subquery_sj_dupsweed_bkaunique...
503
KB
-rw-r--r--
subquery_sj_firstmatch.result
487.96
KB
-rw-r--r--
subquery_sj_firstmatch_bka.res...
488.05
KB
-rw-r--r--
subquery_sj_firstmatch_bka_nix...
480.97
KB
-rw-r--r--
subquery_sj_firstmatch_bkauniq...
488.09
KB
-rw-r--r--
subquery_sj_innodb_all.result
6.38
KB
-rw-r--r--
subquery_sj_innodb_all_bka.res...
6.57
KB
-rw-r--r--
subquery_sj_innodb_all_bka_nix...
6.6
KB
-rw-r--r--
subquery_sj_innodb_all_bkauniq...
6.63
KB
-rw-r--r--
subquery_sj_innodb_none.result
6.66
KB
-rw-r--r--
subquery_sj_innodb_none_bka.re...
6.75
KB
-rw-r--r--
subquery_sj_innodb_none_bka_ni...
6.74
KB
-rw-r--r--
subquery_sj_innodb_none_bkauni...
6.79
KB
-rw-r--r--
subquery_sj_loosescan.result
502.69
KB
-rw-r--r--
subquery_sj_loosescan_bka.resu...
502.79
KB
-rw-r--r--
subquery_sj_loosescan_bka_nixb...
484.38
KB
-rw-r--r--
subquery_sj_loosescan_bkauniqu...
502.82
KB
-rw-r--r--
subquery_sj_mat.result
522.25
KB
-rw-r--r--
subquery_sj_mat_bka.result
522.34
KB
-rw-r--r--
subquery_sj_mat_bka_nixbnl.res...
505.5
KB
-rw-r--r--
subquery_sj_mat_bkaunique.resu...
522.38
KB
-rw-r--r--
subquery_sj_mat_nosj.result
549.88
KB
-rw-r--r--
subquery_sj_none.result
516.26
KB
-rw-r--r--
subquery_sj_none_bka.result
516.35
KB
-rw-r--r--
subquery_sj_none_bka_nixbnl.re...
509.41
KB
-rw-r--r--
subquery_sj_none_bkaunique.res...
516.38
KB
-rw-r--r--
subselect_debug.result
662
B
-rw-r--r--
subselect_gis.result
320
B
-rw-r--r--
subselect_innodb.result
28.76
KB
-rw-r--r--
subselect_notembedded.result
752
B
-rw-r--r--
sum_distinct-big.result
2
KB
-rw-r--r--
sum_distinct.result
3.14
KB
-rw-r--r--
symlink.result
8.12
KB
-rw-r--r--
synchronization.result
4.05
KB
-rw-r--r--
sysdate_is_now.result
76
B
-rw-r--r--
system_mysql_db.result
13.71
KB
-rw-r--r--
system_mysql_db_refs.result
3.14
KB
-rw-r--r--
table_definition_cache_functio...
6.9
KB
-rw-r--r--
table_open_cache_functionality...
12.92
KB
-rw-r--r--
tablelock.result
1.5
KB
-rw-r--r--
tablespace.result
10.57
KB
-rw-r--r--
temp_pool.result
646
B
-rw-r--r--
temp_table.result
8.18
KB
-rw-r--r--
temporal_literal.result
13.62
KB
-rw-r--r--
test_security_context.result
1.06
KB
-rw-r--r--
testdb_only.require
45
B
-rw-r--r--
timezone.result
2.58
KB
-rw-r--r--
timezone2.result
13.21
KB
-rw-r--r--
timezone3.result
2.21
KB
-rw-r--r--
timezone4.result
148
B
-rw-r--r--
timezone_debug.result
926
B
-rw-r--r--
timezone_grant.result
4.42
KB
-rw-r--r--
trans_read_only.result
1.38
KB
-rw-r--r--
transaction_isolation.result
4.3
KB
-rw-r--r--
transaction_read_only.result
5.39
KB
-rw-r--r--
trigger-compat.result
7.98
KB
-rw-r--r--
trigger-trans.result
6.45
KB
-rw-r--r--
trigger.result
87.73
KB
-rw-r--r--
trigger_debug.result
627
B
-rw-r--r--
trigger_notembedded.result
19.95
KB
-rw-r--r--
trigger_wl3253.result
26.27
KB
-rw-r--r--
trigger_wl6030.result
53.61
KB
-rw-r--r--
true.require
7
B
-rw-r--r--
truncate.result
3.7
KB
-rw-r--r--
truncate_coverage.result
2.4
KB
-rw-r--r--
type_binary.result
9.62
KB
-rw-r--r--
type_bit.result
18.23
KB
-rw-r--r--
type_bit_innodb.result
8.2
KB
-rw-r--r--
type_blob.result
40.47
KB
-rw-r--r--
type_date.result
21.96
KB
-rw-r--r--
type_datetime.result
34.39
KB
-rw-r--r--
type_decimal.result
36.16
KB
-rw-r--r--
type_enum.result
101.64
KB
-rw-r--r--
type_float.result
12.43
KB
-rw-r--r--
type_nchar.result
1.44
KB
-rw-r--r--
type_newdecimal-big.result
678
B
-rw-r--r--
type_newdecimal.result
68.9
KB
-rw-r--r--
type_ranges.result
17.61
KB
-rw-r--r--
type_set.result
4.58
KB
-rw-r--r--
type_string.result
139
B
-rw-r--r--
type_temporal_fractional.resul...
645.37
KB
-rw-r--r--
type_temporal_upgrade.result
13.45
KB
-rw-r--r--
type_time.result
120.35
KB
-rw-r--r--
type_timestamp.result
25.71
KB
-rw-r--r--
type_timestamp_explicit.result
31.6
KB
-rw-r--r--
type_uint.result
380
B
-rw-r--r--
type_varchar.result
22.85
KB
-rw-r--r--
type_year.result
7.16
KB
-rw-r--r--
udf.result
17.31
KB
-rw-r--r--
udf_services.result
499
B
-rw-r--r--
udf_skip_grants.result
223
B
-rw-r--r--
union.result
87.33
KB
-rw-r--r--
unsafe_binlog_innodb.result
4.36
KB
-rw-r--r--
update.result
24.32
KB
-rw-r--r--
upgrade.result
5.28
KB
-rw-r--r--
user_if_exists.result
9.13
KB
-rw-r--r--
user_limits.result
5.82
KB
-rw-r--r--
user_lock.result
23.14
KB
-rw-r--r--
user_var-binlog.result
2.21
KB
-rw-r--r--
user_var.result
18.95
KB
-rw-r--r--
utility_warnings.result
7
KB
-rw-r--r--
validate_password_plugin.resul...
12.25
KB
-rw-r--r--
validate_password_plugin_check...
3.69
KB
-rw-r--r--
varbinary.result
58
KB
-rw-r--r--
variables-big.result
972
B
-rw-r--r--
variables-notembedded.result
5.97
KB
-rw-r--r--
variables-win.result
347
B
-rw-r--r--
variables.result
63.66
KB
-rw-r--r--
variables_community.result
182
B
-rw-r--r--
variables_debug.result
2.13
KB
-rw-r--r--
version_token.result
11.29
KB
-rw-r--r--
version_token_bug21280801.resu...
2.72
KB
-rw-r--r--
version_token_errors.result
2.21
KB
-rw-r--r--
view.result
258
KB
-rw-r--r--
view_alias.result
6.19
KB
-rw-r--r--
view_grant.result
126.95
KB
-rw-r--r--
view_gtid_mode_on_debug.result
1.37
KB
-rw-r--r--
wait_timeout.result
1.07
KB
-rw-r--r--
warnings.result
11.89
KB
-rw-r--r--
warnings_engine_disabled.resul...
475
B
-rw-r--r--
windows.require
7
B
-rw-r--r--
windows.result
1.98
KB
-rw-r--r--
wl3836.result
2.71
KB
-rw-r--r--
wl5928.result
15.47
KB
-rw-r--r--
wl6219-csv.result
5.16
KB
-rw-r--r--
wl6219-innodb.result
5.18
KB
-rw-r--r--
wl6219-memory.result
5.18
KB
-rw-r--r--
wl6219-merge.result
3.87
KB
-rw-r--r--
wl6219-myisam.result
5.18
KB
-rw-r--r--
wl6219-upgrade.result
10.52
KB
-rw-r--r--
wl6301_1_not_windows.result
44
B
-rw-r--r--
wl6301_2_not_windows.result
46
B
-rw-r--r--
wl6301_3.result
89
B
-rw-r--r--
wl6443_deprecation.result
10.81
KB
-rw-r--r--
wl6661.result
6.33
KB
-rw-r--r--
wl6711_heap_to_disk.result
6.07
KB
-rw-r--r--
wl6978.result
214
B
-rw-r--r--
xa.result
13.51
KB
-rw-r--r--
xa_deadlock_binlog.result
3.08
KB
-rw-r--r--
xa_debug.result
3.18
KB
-rw-r--r--
xa_gtid.result
878
B
-rw-r--r--
xa_prepared_binlog_off.result
19.77
KB
-rw-r--r--
xml.result
41.94
KB
-rw-r--r--
Delete
Unzip
Zip
${this.title}
Close
Code Editor : opt_hints_subquery.result
# WL#8244 Hints for subquery execution CREATE TABLE t1 (a INTEGER NOT NULL, b INT, PRIMARY KEY (a)); CREATE TABLE t2 (a INTEGER NOT NULL, KEY (a)); CREATE TABLE t3 (a INTEGER NOT NULL, b INT, KEY (a)); INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); INSERT INTO t2 VALUES (2), (3), (4), (5); INSERT INTO t3 VALUES (10,3), (20,4), (30,5); ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK test.t2 analyze status OK test.t3 analyze status OK This query will normally use Table Pull-out EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) Check that we can disable SEMIJOIN transformation EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Same with hint in outer query EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Query with two sub-queries EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT a FROM t1 tx) AND t3.b IN (SELECT a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tx NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`tx`.`a`)) No SEMIJOIN transformation for first subquery EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx) AND t3.b IN (SELECT a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and <in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY)))) EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(`subq1`) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and <in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY)))) No SEMIJOIN transformation for latter subquery EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT a FROM t1 tx) AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 1 PRIMARY tx NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#3`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY)))) EXPLAIN SELECT /*+ NO_SEMIJOIN(@`subq2`) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 1 PRIMARY tx NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY)))) No SEMIJOIN transformation for any subquery EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx) AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) NO_SEMIJOIN(@`select#3`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY)))) EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) NO_SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY)))) Query with nested sub-queries EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 NULL 1 SIMPLE tx NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ty NULL eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and (`test`.`ty`.`a` = `test`.`tx`.`b`)) No SEMIJOIN transformation for outer subquery EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY tx NULL eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where 2 DEPENDENT SUBQUERY ty NULL eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ 1 from `test`.`t1` `ty` join `test`.`t1` `tx` where ((`test`.`ty`.`a` = `test`.`tx`.`b`) and (<cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`)))) No SEMIJOIN transformation for inner-most subquery EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 NULL 1 PRIMARY tx NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 3 SUBQUERY ty NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`tx`.`b`,`test`.`tx`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`ty`.`a` from `test`.`t1` `ty` where 1 ), <primary_index_lookup>(`test`.`tx`.`b` in <temporary table> on <auto_key> where ((`test`.`tx`.`b` = `materialized-subquery`.`a`)))))) No SEMIJOIN transformation at all EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) NO_SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY where (<in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))) and (<cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`))))) This query does not support SEMIJOIN. SEMIJOIN hint is ignored EXPLAIN SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`min(a)`))))) This query will get LooseScan by default EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Let's turn off LooseScan, FirstMatch is then SELECTed EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) Let's also turn off FirstMatch, MatLookup is then used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using where; Using index 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t2.a 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`<subquery2>`.`a` = `test`.`t2`.`a`) Let's also turn off Materialization, DuplicateWeedout should then be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) If we turn off all strategies, DuplicateWeedout should still be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Turn off non-used strategies, nothing should change. Still Loosescan EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Test same query with SEMIJOIN hint Forcing LooseScan, should not change anything EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Force FirstMatch EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) Force Materialization EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using where; Using index 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t2.a 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`<subquery2>`.`a` = `test`.`t2`.`a`) Force DuplicateWeedout EXPLAIN SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) If LooseScan is among candidates, it will be used EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Drop LooseScan from list of strategies, FirstMatch will be used EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) Drop FirstMatch, MatLookup is next EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using where; Using index 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t2.a 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`<subquery2>`.`a` = `test`.`t2`.`a`) For this query LooseScan and Materialization is not applicable EXPLAIN SELECT * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`b` = `test`.`t1`.`a`)) Turn off all applicable strategies. DuplicateWeedout should be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Similar with SEMIJOIN hint EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Test multiple subqueries. Default for this query is Loosecan for first and FirstMatch for latter EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Forcing the default strategy should not change anything EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Forcing a strategy for one, may change the other due to cost changes EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Forcing same strategy for both EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t3) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Loosescan for both is not possible, ends up with DuplicateWeedout EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Swap strategies compared to default EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Different subsets of strategies for different subqueries EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) SEMIJOIN(@`subq2` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Vice versa EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Another combination EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION) SEMIJOIN(@`subq2` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Turn off default EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Also turn off 2nd choice. Gives DuplicateWeedout over both EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Also turn off DuplicateWeedout. Materialization is only one left. EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) Force materialization with SEMIJOIN hints instead EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) This query gives DuplicateWeedout over both since combining DuplicateWeedout with another strategy does not seem possible. EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) More alternatives for 2nd subquery gives Materialization for first EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `<subquery2>`.`a`)) A query with nested subqueries which by default is materialized together EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Let's turn off Materialization, DuplicateWeedout is then SELECTed EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`b` = `test`.`t2`.`a`)) Let's also turn off DuplicateWeedout, FirstMatch is then used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t3.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Also turn off FirstMatch. LooseScan not usable. Back to DuplicateWeedout EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT, FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`b` = `test`.`t2`.`a`)) If we turn off all strategies, DuplicateWeedout should still be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`b` = `test`.`t2`.`a`)) Test same query with SEMIJOIN hint Force Materialization, should not change anything EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Force LooseScan, will use DuplicateWeedout EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`b` = `test`.`t2`.`a`)) Force FirstMatch EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t3.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Force DuplicateWeedout EXPLAIN SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`b` = `test`.`t2`.`a`)) If Materialization is among candidates, it will be used EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Drop Materialization. DuplicateWeedout will be used EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 End temporary Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t3`.`b` = `test`.`t2`.`a`)) Drop DuplicateWeedout, FirstMatch is next EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t3.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Strategy hints on inner-most query is ignored since sj-nests are merged EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Ditto EXPLAIN SELECT /*+ SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE <subquery2> NULL ALL NULL NULL NULL NULL NULL 100.00 NULL 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 <subquery2>.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 MATERIALIZED t2 NULL ref a a 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`a` = `<subquery2>`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`b`)) Turn off semijoin for outer subquery. FirstMatch is used for inner EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 NULL ref a a 4 test.t3.b 1 100.00 Using index; FirstMatch(t3) Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t3`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) Do not use FirstMatch for inner EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 SUBQUERY <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t3.b 1 100.00 NULL 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2` FIRSTMATCH) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`<subquery3>`.`a` = `test`.`t3`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) Do not use FirstMatch nor Materialization for inner EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2 FIRSTMATCH, MATERIALIZATION) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 NULL ref a a 4 test.t3.b 1 100.00 Using index; Start temporary; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2` FIRSTMATCH, MATERIALIZATION) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t3`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) LooseScan is last resort EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`test`.`t3`.`b` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) Allow all stragies except default EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT, LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 2 SUBQUERY <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t3.b 1 100.00 NULL 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`<subquery3>`.`a` = `test`.`t3`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) Force a particular strategy EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2` LOOSESCAN) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`test`.`t3`.`b` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) Turn off semijoin for inner-most subquery. FirstMatch is used for outer EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) 3 SUBQUERY t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`a`) and <in_optimizer>(`test`.`t3`.`b`,`test`.`t3`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`t2`.`a` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t3`.`b` in <temporary table> on <auto_key> where ((`test`.`t3`.`b` = `materialized-subquery`.`a`)))))) Do not use FirstMatch for outer EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 PRIMARY <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) NO_SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`<subquery2>`.`a` = `test`.`t1`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t3`.`b`) in t2 on a)))) Do not use FirstMatch nor Materialization for outer EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where; Start temporary 1 PRIMARY t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) NO_SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t3`.`b`) in t2 on a)))) LooseScan can not be used since index scan would not be "covering" EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where; Start temporary 1 PRIMARY t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) NO_SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t3`.`b`) in t2 on a)))) Allow all stragies except default EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT, LOOSESCAN) NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 PRIMARY <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 2 MATERIALIZED t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`<subquery2>`.`a` = `test`.`t1`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t3`.`b`) in t2 on a)))) Force a particular strategy EXPLAIN SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where; Start temporary 1 PRIMARY t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t3`.`b`) in t2 on a)))) Turn off semijoin for both subqueries EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 DEPENDENT SUBQUERY t3 NULL index_subquery a a 4 func 1 100.00 Using where 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t3 on a where (<in_optimizer>(`test`.`t3`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t3`.`b`) in t2 on a))) and (<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`))))) Test hints with prepared statements PREPARE stmt1 FROM "EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)"; EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) DEALLOCATE PREPARE stmt1; Another Prepared Statement test PREPARE stmt1 FROM "EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2))"; EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2` LOOSESCAN) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`test`.`t3`.`b` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 2 SUBQUERY t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 2 SUBQUERY t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2` LOOSESCAN) NO_SEMIJOIN(@`subq1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` semi join (`test`.`t2`) where (`test`.`t3`.`b` = `test`.`t2`.`a`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) DEALLOCATE PREPARE stmt1; SET optimizer_switch = default; Tests with non-default optimizer_switch settings SET optimizer_switch = 'semijoin=off'; No table pull-out for this query EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) This should not change anything EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Force semijoin EXPLAIN SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) Setting strategy should still force semijoin Strategy is ignored since table pull-out is done EXPLAIN SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq` FIRSTMATCH) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) Query with two sub-queries EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT a FROM t1 tx) AND t3.b IN (SELECT a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY)))) SEMIJOIN transformation for first subquery EXPLAIN SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 Using where 1 PRIMARY tx NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY)))) SEMIJOIN transformation for latter subquery EXPLAIN SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and <in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY)))) SEMIJOIN transformation for both subqueries EXPLAIN SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tx NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1`) SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`tx`.`a`)) Query with nested sub-queries EXPLAIN SELECT * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY where (<in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))) and (<cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`))))) SEMIJOIN transformation for outer subquery EXPLAIN SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL a NULL NULL NULL 3 100.00 NULL 1 PRIMARY tx NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 3 SUBQUERY ty NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and <in_optimizer>(`test`.`tx`.`b`,`test`.`tx`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`ty`.`a` from `test`.`t1` `ty` where 1 ), <primary_index_lookup>(`test`.`tx`.`b` in <temporary table> on <auto_key> where ((`test`.`tx`.`b` = `materialized-subquery`.`a`)))))) SEMIJOIN transformation for inner-most subquery EXPLAIN SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY tx NULL eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where 2 DEPENDENT SUBQUERY ty NULL eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ 1 from `test`.`t1` `ty` join `test`.`t1` `tx` where ((`test`.`ty`.`a` = `test`.`tx`.`b`) and (<cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`)))) SEMIJOIN transformation for both EXPLAIN SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 NULL 1 SIMPLE tx NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ty NULL eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2`) SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where ((`test`.`tx`.`a` = `test`.`t3`.`a`) and (`test`.`ty`.`a` = `test`.`tx`.`b`)) Test strategies when some are disabled by optimizer_switch SET optimizer_switch='semijoin=on'; SET optimizer_switch='loosescan=off'; This query will get LooseScan by default. FirstMatch now. EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) Let's turn off LooseScan also by hint, FirstMatch should still be SELECTed EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) Let's also turn off FirstMatch, MatLookup should then be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using where; Using index 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t2.a 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`<subquery2>`.`a` = `test`.`t2`.`a`) Let's also turn off Materialization, DuplicateWeedout should then be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Let's force LooseScan back on EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Forcing another strategy EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using where; Using index 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t2.a 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`<subquery2>`.`a` = `test`.`t2`.`a`) If LooseScan is among candidates, it is used even if originally disabled EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Disable another strategy SET optimizer_switch='firstmatch=off'; Turn on FirstMatch, but not LooseScan on with hint EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) Drop all remaining strategies with hint, should use DuplicateWeedout EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) For this query LooseScan and Materialization is not applicable Should use DuplicateWeedout since FirstMatch is disabled EXPLAIN SELECT * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Turn off all applicable strategies. DuplicateWeedout should still be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Reverse which strategies are allowed with hint EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`b` = `test`.`t1`.`a`)) Default for this query is Loosecan for first and FirstMatch for latter Since both strategies are disabled, will now use DuplicateWeedout EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Allowing LooseScan and FirstMatch and optimizer_switch is ignored EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Forcing a disabled strategy for one EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Forcing same strategy for both EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t3) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Swap strategies compared to default EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Different subsets of strategies for different subqueries EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) SEMIJOIN(@`subq2` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) Turn off DuplicateWeedout for both. Materialization is left EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT) NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` DUPSWEEDOUT) NO_SEMIJOIN(@`subq2` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) Forcing materialization should have same effect EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) Turn off DuplicateWeedout for first. MatLookup is used for both EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) Turn off DuplicateWeedout for second. Same effect. EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) Enable all strategies except DuplicateWeedout SET optimizer_switch='firstmatch=on,loosescan=on,materialization=on,duplicateweedout=off'; If we turn off all other strategies, DuplicateWeedout will be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) LooseScan and Materialization is not applicable, FirstMatch is used EXPLAIN SELECT * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`b` = `test`.`t1`.`a`)) Turn off all applicable strategies. DuplicateWeedout should be used EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Similar with SEMIJOIN hint EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Disable all strategies SET optimizer_switch='firstmatch=off,loosescan=off,materialization=off,duplicateweedout=off'; DuplicateWeedout is then used EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Turning off extra strategies should not change anything EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t2 NULL ref a a 4 test.t3.a 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`) Turning on some strategies should give one of those EXPLAIN SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2) Warnings: Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t3`.`a` = `test`.`t2`.`a`) For this query that cannot use LooseScan or Materialization, turning those on will still give DupliateWeedout EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 100.00 Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t1`.`b` = `test`.`t3`.`a`) and (`test`.`t1`.`a` = `test`.`t3`.`b`)) Turning on FirstMatch should give FirstMatch EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1 WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 NULL 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`b` = `test`.`t1`.`a`)) SET optimizer_switch = default; Test that setting optimizer_switch after prepare will change strategy PREPARE stmt1 FROM "EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)"; EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) SET optimizer_switch = 'duplicateweedout=off'; Will now use materialization EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 4 test.t1.a 1 100.00 NULL 1 SIMPLE <subquery3> NULL eq_ref <auto_key> <auto_key> 4 test.t1.b 1 100.00 NULL 2 MATERIALIZED t3 NULL index a a 4 NULL 3 100.00 Using index 3 MATERIALIZED t2 NULL index a a 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`<subquery3>`.`a` = `test`.`t1`.`b`) and (`<subquery2>`.`a` = `test`.`t1`.`a`)) SET optimizer_switch = 'duplicateweedout=on'; Turn DuplicateWeedout back on EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; Start temporary 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; End temporary Warnings: Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) DEALLOCATE PREPARE stmt1; SET optimizer_switch = default; Specifying two SEMIJOIN/NO_SEMIJOIN for same query block gives warning First has effect, second is ignored EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SEMIJOIN( ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Try opposite order EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SEMIJOIN() NO_SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Warning 3126 Hint NO_SEMIJOIN( ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) Specify at different levels, hint inside block has effect EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Warning 3126 Hint NO_SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) Specify at different levels, opposite order EXPLAIN SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Duplicate hints also gives warning, but hint has effect EXPLAIN SELECT /*+ SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Warning 3126 Hint SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint NO_SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Multiple subqueries with conflicting hints EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN() */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 2 SUBQUERY t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Warning 3126 Hint SEMIJOIN(@`subq1` LOOSESCAN) is ignored as conflicting/duplicated Warning 3126 Hint SEMIJOIN(@`subq2` FIRSTMATCH) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`)))))) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN(LOOSESCAN) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Warning 3126 Hint SEMIJOIN(@`subq1` LOOSESCAN) is ignored as conflicting/duplicated Warning 3126 Hint SEMIJOIN(@`subq2` FIRSTMATCH) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Conflicting hints in same hint comment EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Warning 3126 Hint SEMIJOIN(@`subq1` FIRSTMATCH) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Warning 3126 Hint NO_SEMIJOIN(@`subq1` LOOSESCAN) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index; LooseScan 1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE t3 NULL ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1) Warnings: Warning 3126 Hint NO_SEMIJOIN(@`subq1` FIRSTMATCH) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`)) Non-supported strategies should give warnings EXPLAIN SELECT /*+ SEMIJOIN(@subq1 INTOEXISTS) NO_SEMIJOIN(@subq2 INTOEXISTS) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 NULL index a a 4 NULL 3 100.00 Using index; LooseScan 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where 1 SIMPLE t2 NULL ref a a 4 test.t1.b 1 100.00 Using index; FirstMatch(t1) Warnings: Warning 1064 Optimizer hint syntax error near 'INTOEXISTS) NO_SEMIJOIN(@subq2 INTOEXISTS) */ * FROM t1 WHERE t1.a IN (SELECT /*' at line 2 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = `test`.`t3`.`a`)) SUBQUERY tests SUBQUERY should disable SEMIJOIN and use specified subquery strategy EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) EXPLAIN SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) Query with two subqueries EXPLAIN SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 3 SUBQUERY ty NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq1` INTOEXISTS) SUBQUERY(@`subq2` MATERIALIZATION) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))) and <in_optimizer>(`test`.`t3`.`b`,`test`.`t3`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`ty`.`a` from `test`.`t1` `ty` where 1 ), <primary_index_lookup>(`test`.`t3`.`b` in <temporary table> on <auto_key> where ((`test`.`t3`.`b` = `materialized-subquery`.`a`)))))) Query with nested sub-queries EXPLAIN SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 DEPENDENT SUBQUERY tx NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where 3 SUBQUERY ty NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq2` MATERIALIZATION) SUBQUERY(@`subq1` INTOEXISTS) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY where (<in_optimizer>(`test`.`tx`.`b`,`test`.`tx`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`ty`.`a` from `test`.`t1` `ty` where 1 ), <primary_index_lookup>(`test`.`tx`.`b` in <temporary table> on <auto_key> where ((`test`.`tx`.`b` = `materialized-subquery`.`a`))))) and (<cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`))))) EXPLAIN SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION) SUBQUERY(@subq2 INTOEXISTS) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY tx NULL ALL PRIMARY NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY ty NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq2` INTOEXISTS) SUBQUERY(@`subq1` MATERIALIZATION) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`a`,`test`.`t3`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`tx`.`a` from `test`.`t1` `tx` where <in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))) ), <primary_index_lookup>(`test`.`t3`.`a` in <temporary table> on <auto_key> where ((`test`.`t3`.`a` = `materialized-subquery`.`a`))))) This query does not support SEMIJOIN. Materialization is default EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`min(a)`))))) Use In-to-exists instead EXPLAIN SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(min(`test`.`t1`.`a`))))) For this query In-to-exists is default EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) AS `a IN (SELECT a FROM t1)` from `test`.`t2` Force Subquery Materialization EXPLAIN SELECT a, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` MATERIALIZATION) */ `test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) AS `a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1)` from `test`.`t2` EXPLAIN SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ a, a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) AS `a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1)` from `test`.`t2` This query does not support Subquery Materialization due to type mismatch EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(sum(`test`.`t1`.`b`))))) Trying to force Subquery Materialization will not change anything EXPLAIN SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 NULL Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(sum(`test`.`t1`.`b`))))) Test hints with prepared statements PREPARE stmt1 FROM "EXPLAIN SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION) SUBQUERY(@subq2 INTOEXISTS) */ * FROM t1 WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3) AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)"; EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index 2 SUBQUERY t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq1` MATERIALIZATION) SUBQUERY(@`subq2` INTOEXISTS) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) and <in_optimizer>(`test`.`t1`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`b`) in t2 on a)))) EXECUTE stmt1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t2 NULL index_subquery a a 4 func 1 100.00 Using index 2 SUBQUERY t3 NULL index a a 4 NULL 3 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq1` MATERIALIZATION) SUBQUERY(@`subq2` INTOEXISTS) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t3`.`a` from `test`.`t3` where 1 ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on <auto_key> where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) and <in_optimizer>(`test`.`t1`.`b`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`b`) in t2 on a)))) DEALLOCATE PREPARE stmt1; Test optimizer_switch settings with SUBQUERY hint SET optimizer_switch='materialization=off'; This query will now use In-to-exist EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ 1 from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(min(`test`.`t1`.`a`))))) Force it to use Materialization EXPLAIN SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`min(a)`))))) SET optimizer_switch='materialization=on,subquery_materialization_cost_based=off'; This query will now use materialization EXPLAIN SELECT a, a IN (SELECT a FROM t1) FROM t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) AS `a IN (SELECT a FROM t1)` from `test`.`t2` Force In-to-exists EXPLAIN SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ a, a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) AS `a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1)` from `test`.`t2` Specifying both strategies should give a warning EXPLAIN SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION, INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION, INTOEXISTS) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tx NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index Warnings: Warning 1064 Optimizer hint syntax error near ', INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION, INTOEXISTS) */ * FROM t3 WHERE t3' at line 2 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`tx`.`a`)) Non-supported strategies should give warnings EXPLAIN SELECT /*+ SUBQUERY(@subq1 FIRSTMATCH) SUBQUERY(@subq2 LOOSESCAN) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx) AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tx NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index 1 SIMPLE t3 NULL ALL a NULL NULL NULL 3 33.33 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE ty NULL eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index Warnings: Warning 1064 Optimizer hint syntax error near 'FIRSTMATCH) SUBQUERY(@subq2 LOOSESCAN) */ * FROM t3 WHERE t3.a IN (SELECT /*+ QB' at line 2 Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where ((`test`.`ty`.`a` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`tx`.`a`)) SET optimizer_switch= default; Specifying two SUBQUERY for same query block gives warning First has effect, second is ignored EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) SUBQUERY(INTOEXISTS) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY( INTOEXISTS) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) Try opposite order EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SUBQUERY(MATERIALIZATION) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY( MATERIALIZATION) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Specify at different levels, hint inside block has effect EXPLAIN SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(INTOEXISTS) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY(@`subq` MATERIALIZATION) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Specify at different levels, opposite order EXPLAIN SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(MATERIALIZATION) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 SUBQUERY t1 NULL index PRIMARY PRIMARY 4 NULL 4 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY(@`subq` INTOEXISTS) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ `test`.`t1`.`a` from `test`.`t1` where 1 ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on <auto_key> where ((`test`.`t2`.`a` = `materialized-subquery`.`a`))))) Specifying combinations of SUBQUERY and SEMIJOIN/NO_SEMIJOIN for same query block gives warning First has effect, second is ignored EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SEMIJOIN( ) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Try opposite order EXPLAIN SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SUBQUERY(MATERIALIZATION) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY( MATERIALIZATION) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) Specify at different levels, hint inside block has effect EXPLAIN SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 4 NULL 4 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.a 1 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY(@`subq` MATERIALIZATION) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`) EXPLAIN SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SUBQUERY(@`subq` INTOEXISTS) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) EXPLAIN SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(@subq INTOEXISTS) */ a FROM t1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 NULL index NULL a 4 NULL 4 100.00 Using where; Using index 2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index Warnings: Warning 3126 Hint SEMIJOIN(@`subq` FIRSTMATCH) is ignored as conflicting/duplicated Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))) drop table t1, t2, t3;
Close