⚝
One Hat Cyber Team
⚝
Your IP:
216.73.216.124
Server IP:
50.28.103.30
Server:
Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software:
nginx/1.28.0
PHP Version:
8.3.12
Buat File
|
Buat Folder
Eksekusi
Dir :
~
/
www
/
server
/
mysql
/
mysql-test
/
include
/
View File Name :
cond_filter_queries.inc
--echo --echo ### Test that optimizer_condition_fanout_filter works ### set optimizer_switch='condition_fanout_filter=off'; --echo EXPLAIN SELECT * FROM t1 WHERE col3=5; EXPLAIN SELECT * FROM t1 WHERE col1_idx>1; EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t1.col1_idx=t2.col1_idx; set optimizer_switch='condition_fanout_filter=on'; --echo EXPLAIN SELECT * FROM t1 WHERE col3=5; --echo ######################################################### --echo --echo # Testing non-indexed Operators --echo --echo # Simple operands EXPLAIN SELECT * FROM t1 WHERE t1.col3=5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<=>5; EXPLAIN SELECT * FROM t1 WHERE t1.col3>5; EXPLAIN SELECT * FROM t1 WHERE t1.col3>=5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<=5; --echo --echo # NOT <simple operands> EXPLAIN SELECT * FROM t1 WHERE t1.col3!=5; EXPLAIN SELECT * FROM t1 WHERE NOT t1.col3<=5; --echo --echo # BETWEEN EXPLAIN SELECT * FROM t1 WHERE t1.col3 BETWEEN 5 AND 10; --echo --echo # NOT BETWEEN EXPLAIN SELECT * FROM t1 WHERE t1.col3 NOT BETWEEN 5 AND 10; --echo --echo # <column> IN EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (5, 6); EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (5, 6, 7, 8); --echo # Dependent subquery in IN list. No filtering in t1 EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (1, (SELECT col3 FROM t2 where col3=t1.col4 LIMIT 1)); --echo --echo # <column> NOT IN EXPLAIN SELECT * FROM t1 WHERE t1.col3 NOT IN (5, 6); EXPLAIN SELECT * FROM t1 WHERE t1.col3 NOT IN (5, 6, 7, 8); --echo --echo # <row_value> IN EXPLAIN SELECT * FROM t1 WHERE (t1.col3, t1.col4) IN ((5,5),(6,6),(7,7)); EXPLAIN SELECT * FROM t1 WHERE (t1.col3, t1.col4) IN ((5,5),(6,6),(7,7),(8,8),(9,9),(10,10)); EXPLAIN SELECT * FROM t1 WHERE (t1.col3, 1) IN ((5,5),(6,6),(7,7)); --echo --echo # <row_value> NOT IN EXPLAIN SELECT * FROM t1 WHERE (t1.col3, t1.col4) NOT IN ((5,5),(6,6),(7,7)); EXPLAIN SELECT * FROM t1 WHERE (t1.col3, 1) NOT IN ((5,5),(6,6),(7,7)); --echo --echo # NULL EXPLAIN SELECT * FROM t1 WHERE t1.col3 IS NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col3 = NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col3>NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col3>=NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col3<NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col3<=NULL; EXPLAIN SELECT * FROM t1 WHERE NULL>t1.col3; EXPLAIN SELECT * FROM t1 WHERE NULL>=t1.col3; EXPLAIN SELECT * FROM t1 WHERE NULL<t1.col3; EXPLAIN SELECT * FROM t1 WHERE NULL<=t1.col3; --echo --echo # NOT NULL EXPLAIN SELECT * FROM t1 WHERE t1.col3 IS NOT NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col3 != NULL; --echo --echo # NULL (not nullable column) EXPLAIN SELECT * FROM t1 WHERE t1.col4 IS NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col4 = NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col4>NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col4>=NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col4<NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col4<=NULL; EXPLAIN SELECT * FROM t1 WHERE NULL>t1.col4; EXPLAIN SELECT * FROM t1 WHERE NULL>=t1.col4; EXPLAIN SELECT * FROM t1 WHERE NULL<t1.col4; EXPLAIN SELECT * FROM t1 WHERE NULL<=t1.col4; --echo --echo # NOT NULL (not nullable column) EXPLAIN SELECT * FROM t1 WHERE t1.col4 IS NOT NULL; EXPLAIN SELECT * FROM t1 WHERE t1.col4 != NULL; --echo --echo # LIKE EXPLAIN SELECT * FROM t1 WHERE t1.vc LIKE 'america'; EXPLAIN SELECT * FROM t1 WHERE t1.vc LIKE '%america'; EXPLAIN SELECT * FROM t1 WHERE t1.vc LIKE 'amer%'; EXPLAIN SELECT * FROM t1 WHERE t1.vc NOT LIKE 'america'; EXPLAIN SELECT * FROM t1 WHERE t1.vc NOT LIKE '%america'; EXPLAIN SELECT * FROM t1 WHERE t1.vc NOT LIKE 'amer%'; --echo --echo # FULLTEXT EXPLAIN SELECT * FROM t1 WHERE MATCH(t1.vc) AGAINST ('+norway' IN BOOLEAN MODE); EXPLAIN SELECT * FROM t1 WHERE MATCH(t1.vc_ft) AGAINST ('+norway'); EXPLAIN SELECT * FROM t1 WHERE MATCH(t1.vc_ft) AGAINST ('norway'); EXPLAIN SELECT * FROM t1 WHERE NOT MATCH(t1.vc) AGAINST ('+norway' IN BOOLEAN MODE); EXPLAIN SELECT * FROM t1 WHERE NOT MATCH(t1.vc_ft) AGAINST ('+norway'); EXPLAIN SELECT * FROM t1 WHERE NOT MATCH(t1.vc_ft) AGAINST ('norway'); --echo --echo # Functions EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) > 3; EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) = 3; EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) IS NULL; EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) IS NOT NULL; --echo --echo # AND/OR/XOR EXPLAIN SELECT * FROM t1 WHERE t1.col3<5; EXPLAIN SELECT * FROM t1 WHERE t1.col4<5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 OR col4<5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 AND col4<5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 XOR col4<5; EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 XOR col4<5 XOR col1_idx>3; EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 XOR (col4<5 AND col4>1); --echo # Done OP non-indexed tests --echo --echo ### START SUBQUERY LOOP ### let $iterations=2; while ($iterations) { # Should there be a filter on semijoin nests? No - #rows=1 is enough --echo --echo # table scan in subq, no filter EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2); --echo --echo # table scan in subq, filter from range access EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2 where col1_idx>2); --echo --echo # table scan subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2 where col3=3); --echo --echo # range in subquery, no filter EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col1_idx>2); --echo --echo # table scan subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col3=3); --echo --echo # range in subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col1_idx>2 and col3=3); --echo --echo # EXISTS - outer reference --echo # dynamic range subq, filter SEL(>) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 where col1_idx>t1.col3); --echo --echo # EXPLAIN SELECT * FROM t1 WHERE col3 = (SELECT 1 FROM t2); EXPLAIN SELECT * FROM t1 WHERE col3 <=> (SELECT 1 FROM t2); EXPLAIN SELECT * FROM t1 WHERE col3 > (SELECT 1 FROM t2); EXPLAIN SELECT * FROM t1 WHERE col3 >= (SELECT 1 FROM t2); EXPLAIN SELECT * FROM t1 WHERE col3 < (SELECT 1 FROM t2); EXPLAIN SELECT * FROM t1 WHERE col3 <= (SELECT 1 FROM t2); EXPLAIN SELECT * FROM t1 WHERE col3 between (SELECT 1 FROM t2) and 2; EXPLAIN SELECT * FROM t1 WHERE col1_idx = (SELECT 1 FROM t2 LIMIT 1); EXPLAIN SELECT * FROM t1 WHERE col1_idx <=> (SELECT 1 FROM t2 LIMIT 1); EXPLAIN SELECT * FROM t1 WHERE col1_idx > (SELECT 1 FROM t2 LIMIT 1); EXPLAIN SELECT * FROM t1 WHERE col1_idx >= (SELECT 1 FROM t2 LIMIT 1); EXPLAIN SELECT * FROM t1 WHERE col1_idx < (SELECT 1 FROM t2 LIMIT 1); EXPLAIN SELECT * FROM t1 WHERE col1_idx <= (SELECT 1 FROM t2 LIMIT 1); EXPLAIN SELECT * FROM t1 WHERE col1_idx between (SELECT 1 FROM t2 LIMIT 1) and 2; --echo set optimizer_switch='semijoin=off'; --echo dec $iterations; } set optimizer_switch=default; --echo ################ --echo EXPLAIN SELECT * FROM t1 AS t1a JOIN t1 AS t1b ON t1a.col1_idx=t1b.col1_idx WHERE t1b.col3=5; --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t2.col3=5; --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t1.col3=t2.col3; --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t1.col2_idx=1 AND t2.col2_idx=1; --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t1.col2_idx>1 AND t2.col2_idx>1; --echo # t2(ALL)-t1(DR) # t1 filter = Conjunction of DEFAULT (Join condition) and range estimate EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx WHERE t1.col2_idx>1 AND t2.col2_idx>1; --echo # t2(ALL)-t1(ALL) # t1 filter = range estimate (Join condition is ignored) EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx WHERE t1.col1_idx>1 AND t2.col1_idx>1; --echo # # EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx WHERE t1.col1_idx!=1 AND t2.col1_idx>4; --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx; --echo EXPLAIN SELECT * FROM t1 WHERE col1_idx > 500 AND col2_idx > 500; --echo EXPLAIN SELECT * FROM t1 WHERE col1_idx > 120 AND col2_idx > 120; --echo # filter effect from indexed column IN EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col2_idx IN (120,121,122); --echo # TODO: after wl7019 --echo # EXPLAIN --echo # SELECT * FROM t1 WHERE col1_idx IN (120,121,122); --echo # EXPLAIN --echo # SELECT * FROM t1 WHERE (col1_idx,c) IN ((120,1),(121,2),(122,3)); ## working on # filter effect from indexed column NOT IN --echo --echo range on col1_idx and filtering estimate from the range optimizer on col2_idx --echo -> no filtering effect for filter_single_col_small --echo -> very small filtering effect for filter_single_col_big EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col2_idx NOT IN (120,121,122); # filter effect from unindexed column IN --echo EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col3 IN (120,121,122); # filter effect from unindexed column NOT IN --echo EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col3 NOT IN (120,121,122); --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col3=t2.col1_idx WHERE t2.col1_idx>20; --echo EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col3=t2.col1_idx WHERE t2.col1_idx=20;