73
73
1 SIMPLE t1 pnull system NULL NULL NULL NULL 1
74
74
explain partitions select * from t1 where a >= 0;
75
75
id select_type table partitions type possible_keys key key_len ref rows Extra
76
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 3 Using where
76
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
77
77
explain partitions select * from t1 where a < 0;
78
78
id select_type table partitions type possible_keys key key_len ref rows Extra
79
79
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
80
80
explain partitions select * from t1 where a <= 0;
81
81
id select_type table partitions type possible_keys key key_len ref rows Extra
82
1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 3 Using where
82
1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where
83
83
explain partitions select * from t1 where a > 1;
84
84
id select_type table partitions type possible_keys key key_len ref rows Extra
85
85
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
113
113
explain partitions select * from t1 where a is null;
114
114
id select_type table partitions type possible_keys key key_len ref rows Extra
115
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 6 Using where
115
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
116
116
explain partitions select * from t1 where a >= 0;
117
117
id select_type table partitions type possible_keys key key_len ref rows Extra
118
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 6 Using where
118
1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
119
119
explain partitions select * from t1 where a < 0;
120
120
id select_type table partitions type possible_keys key key_len ref rows Extra
121
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 6 Using where
121
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
122
122
explain partitions select * from t1 where a <= 0;
123
123
id select_type table partitions type possible_keys key key_len ref rows Extra
124
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 6 Using where
124
1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where
125
125
explain partitions select * from t1 where a > 1;
126
126
id select_type table partitions type possible_keys key key_len ref rows Extra
127
127
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
810
810
drop table t1, t2;
812
# Bug#50939: Loose Index Scan unduly relies on engine to remember range
819
) PARTITION BY HASH (a) PARTITIONS 1;
825
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
826
INSERT INTO t1 SELECT a + 5, b + 5 FROM t1;
827
INSERT INTO t1 SELECT a + 10, b + 10 FROM t1;
828
INSERT INTO t1 SELECT a + 20, b + 20 FROM t1;
829
INSERT INTO t1 SELECT a + 40, b + 40 FROM t1;
830
INSERT INTO t2 SELECT * FROM t1;
831
# plans should be identical
832
EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
833
id select_type table type possible_keys key key_len ref rows Extra
834
1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by
835
EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
836
id select_type table type possible_keys key key_len ref rows Extra
837
1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by
839
SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
842
# Should be no more than 4 reads.
843
SHOW status LIKE 'handler_read_key';
847
SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
850
# Should be no more than 4 reads.
851
SHOW status LIKE 'handler_read_key';