⚝
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
/
r
/
View File Name :
filter_single_col_idx_small.result
CREATE TABLE t1( col1_idx INT DEFAULT NULL, col2_idx INT DEFAULT NULL, col3 INT DEFAULT NULL, col4 INT NOT NULL, vc VARCHAR(20), vc_ft VARCHAR(20), KEY(col1_idx), KEY(col2_idx), FULLTEXT(vc_ft) ) ENGINE=myisam; CREATE TABLE t2( col1_idx INT DEFAULT NULL, col2_idx INT DEFAULT NULL, col3 INT DEFAULT NULL, KEY(col1_idx), KEY(col2_idx) ) ENGINE=myisam; insert into t1 values (1,1,1,1,'america', 'america'),(2,2,2,2,'england','england'); insert into t1 select col1_idx+2, col2_idx+2, col3+2, col4+2, vc, vc_ft from t1; insert into t1 select col1_idx+2*2, col2_idx+2*2, col3+2*2, col4+2*2, vc, vc_ft from t1; insert into t1 select col1_idx+2*2*2, col2_idx+2*2*2, col3+2*2*2, col4+2*2*2, vc, vc_ft from t1; insert into t1 select col1_idx, col2_idx, col3, col4, 'america', 'america' from t1; insert into t1 select col1_idx, col2_idx, col3, col4, 'england america', 'england america' from t1; insert ignore into t1 select col1_idx, col2_idx, col3, col4, 'germany england america', 'germany england america' from t1; insert into t2 select col1_idx,col2_idx,col3 from t1; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status Table is already up to date ### Test that optimizer_condition_fanout_filter works ### set optimizer_switch='condition_fanout_filter=off'; EXPLAIN SELECT * FROM t1 WHERE col3=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where EXPLAIN SELECT * FROM t1 WHERE col1_idx>1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL col1_idx NULL NULL NULL 128 91.41 Using where EXPLAIN SELECT * FROM t1 JOIN t2 WHERE t1.col1_idx=t2.col1_idx; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL col1_idx NULL NULL NULL 128 100.00 Using where 1 SIMPLE t2 NULL ref col1_idx col1_idx 5 test.t1.col1_idx 8 100.00 NULL set optimizer_switch='condition_fanout_filter=on'; EXPLAIN SELECT * FROM t1 WHERE col3=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where ######################################################### # Testing non-indexed Operators # Simple operands EXPLAIN SELECT * FROM t1 WHERE t1.col3=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<=>5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3>5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3>=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where # NOT <simple operands> EXPLAIN SELECT * FROM t1 WHERE t1.col3!=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 90.00 Using where EXPLAIN SELECT * FROM t1 WHERE NOT t1.col3<=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where # BETWEEN EXPLAIN SELECT * FROM t1 WHERE t1.col3 BETWEEN 5 AND 10; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where # NOT BETWEEN EXPLAIN SELECT * FROM t1 WHERE t1.col3 NOT BETWEEN 5 AND 10; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where # <column> IN EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (5, 6); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 20.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (5, 6, 7, 8); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 40.00 Using where # 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)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 128 10.00 Using where # <column> NOT IN EXPLAIN SELECT * FROM t1 WHERE t1.col3 NOT IN (5, 6); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 80.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3 NOT IN (5, 6, 7, 8); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 60.00 Using where # <row_value> IN EXPLAIN SELECT * FROM t1 WHERE (t1.col3, t1.col4) IN ((5,5),(6,6),(7,7)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 3.00 Using where EXPLAIN SELECT * FROM t1 WHERE (t1.col3, t1.col4) IN ((5,5),(6,6),(7,7),(8,8),(9,9),(10,10)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 6.00 Using where EXPLAIN SELECT * FROM t1 WHERE (t1.col3, 1) IN ((5,5),(6,6),(7,7)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 30.00 Using where # <row_value> NOT IN EXPLAIN SELECT * FROM t1 WHERE (t1.col3, t1.col4) NOT IN ((5,5),(6,6),(7,7)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 97.00 Using where EXPLAIN SELECT * FROM t1 WHERE (t1.col3, 1) NOT IN ((5,5),(6,6),(7,7)); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 70.00 Using where # NULL EXPLAIN SELECT * FROM t1 WHERE t1.col3 IS NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3 = NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3>NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3>=NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<=NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL>t1.col3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL>=t1.col3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL<t1.col3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL<=t1.col3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where # NOT NULL EXPLAIN SELECT * FROM t1 WHERE t1.col3 IS NOT NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 90.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3 != NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 90.00 Using where # NULL (not nullable column) EXPLAIN SELECT * FROM t1 WHERE t1.col4 IS NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN SELECT * FROM t1 WHERE t1.col4 = NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col4>NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col4>=NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col4<NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col4<=NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL>t1.col4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL>=t1.col4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL<t1.col4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE NULL<=t1.col4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where # NOT NULL (not nullable column) EXPLAIN SELECT * FROM t1 WHERE t1.col4 IS NOT NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 90.00 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col4 != NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 90.00 Using where # LIKE EXPLAIN SELECT * FROM t1 WHERE t1.vc LIKE 'america'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where EXPLAIN SELECT * FROM t1 WHERE t1.vc LIKE '%america'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where EXPLAIN SELECT * FROM t1 WHERE t1.vc LIKE 'amer%'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where EXPLAIN SELECT * FROM t1 WHERE t1.vc NOT LIKE 'america'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where EXPLAIN SELECT * FROM t1 WHERE t1.vc NOT LIKE '%america'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where EXPLAIN SELECT * FROM t1 WHERE t1.vc NOT LIKE 'amer%'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where # FULLTEXT EXPLAIN SELECT * FROM t1 WHERE MATCH(t1.vc) AGAINST ('+norway' IN BOOLEAN MODE); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where EXPLAIN SELECT * FROM t1 WHERE MATCH(t1.vc_ft) AGAINST ('+norway'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL fulltext vc_ft vc_ft 0 const 1 100.00 Using where EXPLAIN SELECT * FROM t1 WHERE MATCH(t1.vc_ft) AGAINST ('norway'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL fulltext vc_ft vc_ft 0 const 1 100.00 Using where EXPLAIN SELECT * FROM t1 WHERE NOT MATCH(t1.vc) AGAINST ('+norway' IN BOOLEAN MODE); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where EXPLAIN SELECT * FROM t1 WHERE NOT MATCH(t1.vc_ft) AGAINST ('+norway'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where EXPLAIN SELECT * FROM t1 WHERE NOT MATCH(t1.vc_ft) AGAINST ('norway'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 88.89 Using where # Functions EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) > 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) = 3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) IS NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where EXPLAIN SELECT * FROM t1 WHERE length(t1.vc) IS NOT NULL; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where # AND/OR/XOR EXPLAIN SELECT * FROM t1 WHERE t1.col3<5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col4<5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 33.33 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 OR col4<5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 55.55 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 AND col4<5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 XOR col4<5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 44.44 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 XOR col4<5 XOR col1_idx>3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 48.15 Using where EXPLAIN SELECT * FROM t1 WHERE t1.col3<5 XOR (col4<5 AND col4>1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 37.03 Using where # Done OP non-indexed tests ### START SUBQUERY LOOP ### # table scan in subq, no filter EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 5 test.t1.col3 1 100.00 NULL 2 MATERIALIZED t2 NULL ALL NULL NULL NULL NULL 128 100.00 NULL # table scan in subq, filter from range access EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2 where col1_idx>2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 5 test.t1.col3 1 100.00 NULL 2 MATERIALIZED t2 NULL ALL col1_idx NULL NULL NULL 128 91.41 Using where # table scan subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2 where col3=3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where 1 SIMPLE <subquery2> NULL eq_ref <auto_key> <auto_key> 5 test.t1.col3 1 100.00 Using where 2 MATERIALIZED t2 NULL ALL NULL NULL NULL NULL 128 10.00 Using where # range in subquery, no filter EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col1_idx>2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 NULL 2 SUBQUERY t2 NULL range col1_idx col1_idx 5 NULL 117 100.00 Using index condition # table scan subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col3=3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 NULL 2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 128 10.00 Using where # range in subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col1_idx>2 and col3=3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 NULL 2 SUBQUERY t2 NULL range col1_idx col1_idx 5 NULL 117 10.00 Using index condition; Using where # EXISTS - outer reference # dynamic range subq, filter SEL(>) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 where col1_idx>t1.col3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 2 DEPENDENT SUBQUERY t2 NULL ALL col1_idx NULL NULL NULL 128 33.33 Range checked for each record (index map: 0x1) # EXPLAIN SELECT * FROM t1 WHERE col3 = (SELECT 1 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 128 10.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 <=> (SELECT 1 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 128 10.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 > (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 >= (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 < (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 <= (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 between (SELECT 1 FROM t2) and 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx = (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ref col1_idx col1_idx 5 const 11 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx <=> (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ref col1_idx col1_idx 5 const 11 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx > (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL col1_idx NULL NULL NULL 128 91.41 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx >= (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL col1_idx NULL NULL NULL 128 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx < (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range col1_idx col1_idx 5 NULL 1 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx <= (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range col1_idx col1_idx 5 NULL 11 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx between (SELECT 1 FROM t2 LIMIT 1) and 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range col1_idx col1_idx 5 NULL 22 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index set optimizer_switch='semijoin=off'; # table scan in subq, no filter EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 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 128 100.00 Using where 2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 128 100.00 NULL # table scan in subq, filter from range access EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2 where col1_idx>2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 2 SUBQUERY t2 NULL ALL col1_idx NULL NULL NULL 128 91.41 Using where # table scan subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE t1.col3 IN (SELECT col3 FROM t2 where col3=3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 128 10.00 Using where # range in subquery, no filter EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col1_idx>2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 NULL 2 SUBQUERY t2 NULL range col1_idx col1_idx 5 NULL 117 100.00 Using index condition # table scan subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col3=3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 NULL 2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 128 10.00 Using where # range in subquery, filter SEL(=) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT col3 FROM t2 where col1_idx>2 and col3=3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 NULL 2 SUBQUERY t2 NULL range col1_idx col1_idx 5 NULL 117 10.00 Using index condition; Using where # EXISTS - outer reference # dynamic range subq, filter SEL(>) EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 where col1_idx>t1.col3); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 100.00 Using where 2 DEPENDENT SUBQUERY t2 NULL ALL col1_idx NULL NULL NULL 128 33.33 Range checked for each record (index map: 0x1) # EXPLAIN SELECT * FROM t1 WHERE col3 = (SELECT 1 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 128 10.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 <=> (SELECT 1 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 128 10.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 > (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 >= (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 < (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 <= (SELECT 1 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 128 33.33 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col3 between (SELECT 1 FROM t2) and 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 128 11.11 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx = (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ref col1_idx col1_idx 5 const 11 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx <=> (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ref col1_idx col1_idx 5 const 11 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx > (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL col1_idx NULL NULL NULL 128 91.41 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx >= (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL ALL col1_idx NULL NULL NULL 128 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx < (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range col1_idx col1_idx 5 NULL 1 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx <= (SELECT 1 FROM t2 LIMIT 1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range col1_idx col1_idx 5 NULL 11 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index EXPLAIN SELECT * FROM t1 WHERE col1_idx between (SELECT 1 FROM t2 LIMIT 1) and 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 NULL range col1_idx col1_idx 5 NULL 22 100.00 Using where 2 SUBQUERY t2 NULL index NULL col1_idx 5 NULL 128 100.00 Using index set optimizer_switch='semijoin=off'; set optimizer_switch=default; ################ EXPLAIN SELECT * FROM t1 AS t1a JOIN t1 AS t1b ON t1a.col1_idx=t1b.col1_idx WHERE t1b.col3=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1b NULL ALL col1_idx NULL NULL NULL 128 10.00 Using where 1 SIMPLE t1a NULL ref col1_idx col1_idx 5 test.t1b.col1_idx 8 100.00 NULL EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t2.col3=5; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL col1_idx NULL NULL NULL 128 10.00 Using where 1 SIMPLE t1 NULL ref col1_idx col1_idx 5 test.t2.col1_idx 8 100.00 NULL EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t1.col3=t2.col3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL col1_idx NULL NULL NULL 128 100.00 Using where 1 SIMPLE t2 NULL ref col1_idx col1_idx 5 test.t1.col1_idx 8 10.00 Using where EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t1.col2_idx=1 AND t2.col2_idx=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ref col1_idx,col2_idx col2_idx 5 const 5 100.00 Using where 1 SIMPLE t1 NULL ref col1_idx,col2_idx col1_idx 5 test.t2.col1_idx 8 8.59 Using where EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx=t2.col1_idx WHERE t1.col2_idx>1 AND t2.col2_idx>1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL col1_idx,col2_idx NULL NULL NULL 128 91.41 Using where 1 SIMPLE t2 NULL ref col1_idx,col2_idx col1_idx 5 test.t1.col1_idx 8 96.09 Using where EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx WHERE t1.col2_idx>1 AND t2.col2_idx>1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL col1_idx,col2_idx NULL NULL NULL 128 91.41 Using where 1 SIMPLE t2 NULL ALL col1_idx,col2_idx NULL NULL NULL 128 32.03 Using where; Using join buffer (Block Nested Loop) EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx WHERE t1.col1_idx>1 AND t2.col1_idx>1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL col1_idx NULL NULL NULL 128 91.41 Using where 1 SIMPLE t2 NULL ALL col1_idx NULL NULL NULL 128 96.09 Using where; Using join buffer (Block Nested Loop) EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx WHERE t1.col1_idx!=1 AND t2.col1_idx>4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL col1_idx NULL NULL NULL 128 82.81 Using where 1 SIMPLE t1 NULL ALL col1_idx NULL NULL NULL 128 92.19 Using where; Using join buffer (Block Nested Loop) EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col1_idx>t2.col1_idx; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL col1_idx NULL NULL NULL 128 100.00 NULL 1 SIMPLE t1 NULL ALL col1_idx NULL NULL NULL 128 33.33 Range checked for each record (index map: 0x1) EXPLAIN SELECT * FROM t1 WHERE col1_idx > 500 AND col2_idx > 500; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col1_idx,col2_idx col1_idx 5 NULL 1 5.00 Using index condition; Using where EXPLAIN SELECT * FROM t1 WHERE col1_idx > 120 AND col2_idx > 120; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col1_idx,col2_idx col1_idx 5 NULL 1 5.00 Using index condition; Using where EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col2_idx IN (120,121,122); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col1_idx,col2_idx col1_idx 5 NULL 3 2.34 Using index condition; Using where # TODO: after wl7019 # EXPLAIN # SELECT * FROM t1 WHERE col1_idx IN (120,121,122); # EXPLAIN # SELECT * FROM t1 WHERE (col1_idx,c) IN ((120,1),(121,2),(122,3)); range on col1_idx and filtering estimate from the range optimizer on col2_idx -> no filtering effect for filter_single_col_small -> 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); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col1_idx,col2_idx col1_idx 5 NULL 3 100.00 Using index condition; Using where EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col3 IN (120,121,122); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col1_idx col1_idx 5 NULL 3 30.00 Using index condition; Using where EXPLAIN SELECT * FROM t1 WHERE col1_idx IN (120,121,122) AND col3 NOT IN (120,121,122); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range col1_idx col1_idx 5 NULL 3 70.00 Using index condition; Using where EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col3=t2.col1_idx WHERE t2.col1_idx>20; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL range col1_idx col1_idx 5 NULL 2 100.00 Using index condition 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where; Using join buffer (Block Nested Loop) EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.col3=t2.col1_idx WHERE t2.col1_idx=20; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ref col1_idx col1_idx 5 const 1 100.00 NULL 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 10.00 Using where; Using join buffer (Block Nested Loop) DROP TABLE t1,t2; # # Test that on a tiny table, the computed filtering effect is not # less than one row for the basic condition filter constants # CREATE TABLE t1( col1_pk INTEGER PRIMARY KEY, col2 INTEGER ); INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); # Filtered column should be 1 / (size of table) and larger than # COND_FILTER_EQUALITY EXPLAIN SELECT * FROM t1 WHERE col2 = 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col1_pk` AS `col1_pk`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col2` = 2) # Filtered column should be (size of table - 1) / (size of table) # and less than COND_FILTER_EQUALITY EXPLAIN SELECT * FROM t1 WHERE col2 <> 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 87.50 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col1_pk` AS `col1_pk`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col2` <> 2) # Filtered column should be 1 / (size of table) and larger than # COND_FILTER_BETWEEN EXPLAIN SELECT * FROM t1 WHERE col2 BETWEEN 2 AND 4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 12.50 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col1_pk` AS `col1_pk`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col2` between 2 and 4) # Filtered column should be (size of table - 1) / (size of table) # and less than COND_FILTER_BETWEEN EXPLAIN SELECT * FROM t1 WHERE col2 NOT BETWEEN 2 AND 4; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 8 87.50 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col1_pk` AS `col1_pk`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col2` not between 2 and 4) TRUNCATE TABLE t1; INSERT INTO t1 VALUES (0,0),(1,1); # Filtered column should be 1 / (size of table) and larger than # COND_FILTER_INEQUALITY EXPLAIN SELECT * FROM t1 WHERE col2 > 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`col1_pk` AS `col1_pk`,`test`.`t1`.`col2` AS `col2` from `test`.`t1` where (`test`.`t1`.`col2` > 1) DROP TABLE t1; # # Bug#18438671: MAKE USE OF DATATYPE CONSTRAINTS WHEN CALCULATING # FILTERING EFFECT # CREATE TABLE t1( day_of_week enum('0','1','2','3','4','5','6'), bit1 bit(1), bit3 bit(3) ) ENGINE=myisam; INSERT INTO t1 VALUES (1+RAND()*7, RAND()*2, RAND()*8), (1+RAND()*7, RAND()*2, RAND()*8); INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1; INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1; INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1; INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1; INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1; INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1; EXPLAIN SELECT * FROM t1 WHERE bit1 = b'1'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit1` = 0x01) EXPLAIN SELECT * FROM t1 WHERE bit1 <=>b'1'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit1` <=> 0x01) EXPLAIN SELECT * FROM t1 WHERE bit1 > b'0'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit1` > 0x00) EXPLAIN SELECT * FROM t1 WHERE bit1 >= b'0'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit1` >= 0x00) EXPLAIN SELECT * FROM t1 WHERE bit1 < b'0'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit1` < 0x00) EXPLAIN SELECT * FROM t1 WHERE bit1 <= b'0'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 50.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit1` <= 0x00) EXPLAIN SELECT * FROM t1 WHERE bit3 = b'1'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 12.50 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`bit3` = 0x01) EXPLAIN SELECT * FROM t1 WHERE day_of_week; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 85.71 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where `test`.`t1`.`day_of_week` EXPLAIN SELECT * FROM t1 WHERE day_of_week = 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 14.29 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`day_of_week` = 1) EXPLAIN SELECT * FROM t1 WHERE day_of_week IN (1); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 14.29 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`day_of_week` = 1) EXPLAIN SELECT * FROM t1 WHERE day_of_week IN (1,2); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 28.57 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`day_of_week` in (1,2)) EXPLAIN SELECT * FROM t1 WHERE day_of_week LIKE 'foo'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 14.29 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`day_of_week` like 'foo') EXPLAIN SELECT * FROM t1 WHERE NOT day_of_week = 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 128 85.71 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`day_of_week` AS `day_of_week`,`test`.`t1`.`bit1` AS `bit1`,`test`.`t1`.`bit3` AS `bit3` from `test`.`t1` where (`test`.`t1`.`day_of_week` <> 1) DROP TABLE t1;