4468 Jorgen Loland 2012-09-13
Bug#14095506: DUBIOUS CHOICE OF INDEXES TO MERGE IN
INDEX_MERGE_MYISAM.TEST
handler::multi_range_read_info_const() uses
index_only_read_time() to estimate io cost when only the index
is accessed. However, if the row estimate is two or less
handler::read_time() is used instead:
if ((*flags & HA_MRR_INDEX_ONLY) && total_rows > 2)
This makes little sense, and also contrasts the io cost
estimate made by handler::multi_range_read_info() even though
the two functions claim to be equal wrt io cost estimate.
Before this patch, it was considered cheaper to read a range
with 3-5 rows than a range with 1-2 rows.
@ mysql-test/r/group_by.result
Tweaked test to avoid QEP change
@ mysql-test/t/group_by.test
Tweaked test to avoid QEP change
modified:
mysql-test/r/func_in_all.result
mysql-test/r/func_in_icp.result
mysql-test/r/func_in_icp_mrr.result
mysql-test/r/func_in_mrr.result
mysql-test/r/func_in_mrr_cost.result
mysql-test/r/func_in_none.result
mysql-test/r/func_like.result
mysql-test/r/group_by.result
mysql-test/r/group_min_max.result
mysql-test/r/index_merge_myisam.result
mysql-test/r/join_outer_innodb.result
mysql-test/r/key.result
mysql-test/r/order_by_all.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/r/partition_pruning.result
mysql-test/r/partition_range.result
mysql-test/r/range_all.result
mysql-test/r/range_icp.result
mysql-test/r/range_icp_mrr.result
mysql-test/r/range_mrr.result
mysql-test/r/range_mrr_cost.result
mysql-test/r/range_none.result
mysql-test/r/select_all.result
mysql-test/r/select_all_bka.result
mysql-test/r/select_icp_mrr.result
mysql-test/r/select_icp_mrr_bka.result
mysql-test/r/select_none.result
mysql-test/r/select_none_bka.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
mysql-test/suite/innodb/r/innodb_mysql.result
mysql-test/suite/opt_trace/r/bugs_no_prot_all.result
mysql-test/suite/opt_trace/r/bugs_no_prot_none.result
mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result
mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result
mysql-test/suite/opt_trace/r/charset.result
mysql-test/suite/opt_trace/r/eq_range_statistics.result
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
mysql-test/suite/opt_trace/r/general_no_prot_all.result
mysql-test/suite/opt_trace/r/general_no_prot_none.result
mysql-test/suite/opt_trace/r/general_ps_prot_all.result
mysql-test/suite/opt_trace/r/general_ps_prot_none.result
mysql-test/suite/opt_trace/r/range_no_prot.result
mysql-test/suite/opt_trace/r/range_ps_prot.result
mysql-test/t/group_by.test
mysql-test/t/group_min_max.test
mysql-test/t/partition_range.test
sql/handler.cc
4467 Nuno Carvalho 2012-09-12 [merge]
BUG#13813811 - SPORADIC FAILURES IN RPL_GTID_STRESS_FAILOVER TEST
Merge from mysql-5.6 into mysql-trunk.
modified:
mysql-test/collections/default.experimental
=== modified file 'mysql-test/r/func_in_all.result'
--- a/mysql-test/r/func_in_all.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/func_in_all.result 2012-09-13 08:12:30 +0000
@@ -521,7 +521,7 @@ a
b
explain select f1 from t1 where f1 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
+1 SIMPLE t1 range t1f1_idx t1f1_idx 2 NULL 2 Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
@@ -552,7 +552,7 @@ Warning 1292 Truncated incorrect DOUBLE
Warning 1292 Truncated incorrect DOUBLE value: 'b'
explain select f2 from t2 where f2 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
+1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
Warning 1292 Truncated incorrect DOUBLE value: 'b'
=== modified file 'mysql-test/r/func_in_icp.result'
--- a/mysql-test/r/func_in_icp.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/func_in_icp.result 2012-09-13 08:12:30 +0000
@@ -521,7 +521,7 @@ a
b
explain select f1 from t1 where f1 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
+1 SIMPLE t1 range t1f1_idx t1f1_idx 2 NULL 2 Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
@@ -552,7 +552,7 @@ Warning 1292 Truncated incorrect DOUBLE
Warning 1292 Truncated incorrect DOUBLE value: 'b'
explain select f2 from t2 where f2 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
+1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
Warning 1292 Truncated incorrect DOUBLE value: 'b'
=== modified file 'mysql-test/r/func_in_icp_mrr.result'
--- a/mysql-test/r/func_in_icp_mrr.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/func_in_icp_mrr.result 2012-09-13 08:12:30 +0000
@@ -521,7 +521,7 @@ a
b
explain select f1 from t1 where f1 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
+1 SIMPLE t1 range t1f1_idx t1f1_idx 2 NULL 2 Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
@@ -552,7 +552,7 @@ Warning 1292 Truncated incorrect DOUBLE
Warning 1292 Truncated incorrect DOUBLE value: 'b'
explain select f2 from t2 where f2 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
+1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
Warning 1292 Truncated incorrect DOUBLE value: 'b'
=== modified file 'mysql-test/r/func_in_mrr.result'
--- a/mysql-test/r/func_in_mrr.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/func_in_mrr.result 2012-09-13 08:12:30 +0000
@@ -521,7 +521,7 @@ a
b
explain select f1 from t1 where f1 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
+1 SIMPLE t1 range t1f1_idx t1f1_idx 2 NULL 2 Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
@@ -552,7 +552,7 @@ Warning 1292 Truncated incorrect DOUBLE
Warning 1292 Truncated incorrect DOUBLE value: 'b'
explain select f2 from t2 where f2 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
+1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
Warning 1292 Truncated incorrect DOUBLE value: 'b'
=== modified file 'mysql-test/r/func_in_mrr_cost.result'
--- a/mysql-test/r/func_in_mrr_cost.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/func_in_mrr_cost.result 2012-09-13 08:12:30 +0000
@@ -521,7 +521,7 @@ a
b
explain select f1 from t1 where f1 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
+1 SIMPLE t1 range t1f1_idx t1f1_idx 2 NULL 2 Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
@@ -552,7 +552,7 @@ Warning 1292 Truncated incorrect DOUBLE
Warning 1292 Truncated incorrect DOUBLE value: 'b'
explain select f2 from t2 where f2 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
+1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
Warning 1292 Truncated incorrect DOUBLE value: 'b'
=== modified file 'mysql-test/r/func_in_none.result'
--- a/mysql-test/r/func_in_none.result 2012-03-06 14:29:42 +0000
+++ b/mysql-test/r/func_in_none.result 2012-09-13 08:12:30 +0000
@@ -520,7 +520,7 @@ a
b
explain select f1 from t1 where f1 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index t1f1_idx t1f1_idx 2 NULL 3 Using where; Using index
+1 SIMPLE t1 range t1f1_idx t1f1_idx 2 NULL 2 Using where; Using index
select f1 from t1 where f1 in (2,1);
f1
1
@@ -551,7 +551,7 @@ Warning 1292 Truncated incorrect DOUBLE
Warning 1292 Truncated incorrect DOUBLE value: 'b'
explain select f2 from t2 where f2 in ('a','b');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index
+1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index
Warnings:
Warning 1292 Truncated incorrect DOUBLE value: 'a'
Warning 1292 Truncated incorrect DOUBLE value: 'b'
=== modified file 'mysql-test/r/func_like.result'
--- a/mysql-test/r/func_like.result 2011-07-19 15:11:15 +0000
+++ b/mysql-test/r/func_like.result 2012-09-13 08:12:30 +0000
@@ -3,12 +3,12 @@ create table t1 (a varchar(10), key(a));
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
explain extended select * from t1 where a like 'abc%';
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index a a 13 NULL 5 20.00 Using where; Using index
+1 SIMPLE t1 range a a 13 NULL 1 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like 'abc%')
explain extended select * from t1 where a like concat('abc','%');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index a a 13 NULL 5 20.00 Using where; Using index
+1 SIMPLE t1 range a a 13 NULL 1 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` like <cache>(concat('abc','%')))
select * from t1 where a like "abc%";
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2012-08-27 11:23:37 +0000
+++ b/mysql-test/r/group_by.result 2012-09-13 08:12:30 +0000
@@ -2654,6 +2654,10 @@ b varchar(1),
KEY (b,a)
);
INSERT INTO t1 VALUES (1,NULL),(0,'a'),(1,NULL),(0,'a');
+INSERT INTO t1 VALUES (1,'a'),(0,'a'),(1,'a'),(0,'a');
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2012-08-23 07:45:33 +0000
+++ b/mysql-test/r/group_min_max.result 2012-09-13 08:12:30 +0000
@@ -2164,6 +2164,11 @@ id2 id3 id5 id4 id3 id6 id5 id1
DROP TABLE t1,t2,t3,t4,t5,t6;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+INSERT INTO t1 VALUES (2,1),(2,2),(2,0),(2,3);
+INSERT INTO t1 VALUES (3,1),(3,2),(3,0),(3,3);
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,b PRIMARY 8 NULL 1 Using where; Using index for group-by
@@ -2175,9 +2180,12 @@ MIN(b) a
2 1
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
INSERT INTO t2 SELECT a,b,b FROM t1;
+ANALYZE TABLE t2;
+Table Op Msg_type Msg_text
+test.t2 analyze status OK
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by
+1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
MIN(c)
2
@@ -2955,7 +2963,7 @@ a c COUNT(DISTINCT c, a, b)
EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2
WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 15 NULL 1 Using where; Using index for group-by
+1 SIMPLE t2 range a a 5 NULL 1 Using where; Using index
SELECT COUNT(DISTINCT c, a, b) FROM t2
WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c;
COUNT(DISTINCT c, a, b)
=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result 2012-08-08 07:48:12 +0000
+++ b/mysql-test/r/index_merge_myisam.result 2012-09-13 08:12:30 +0000
@@ -96,7 +96,7 @@ id select_type table type possible_keys
explain select * from t0 where
(key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i3,i4 4,4 NULL 6 Using sort_union(i3,i4); Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); Using where
explain
select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3);
id select_type table type possible_keys key key_len ref rows Extra
@@ -109,7 +109,7 @@ key1 key2 key3 key4 key5 key6 key7 key8
explain select * from t0 where
(key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i3,i4 4,4 NULL 6 Using sort_union(i3,i4); Using where
+1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i5,i6 4,4 NULL 4 Using sort_union(i5,i6); Using where
explain select * from t0 where
(key1 < 3 or key2 < 3) and (key3 < 70);
id select_type table type possible_keys key key_len ref rows Extra
=== modified file 'mysql-test/r/join_outer_innodb.result'
--- a/mysql-test/r/join_outer_innodb.result 2012-06-28 15:40:13 +0000
+++ b/mysql-test/r/join_outer_innodb.result 2012-09-13 08:12:30 +0000
@@ -10,7 +10,7 @@ EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
+1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
=== modified file 'mysql-test/r/key.result'
--- a/mysql-test/r/key.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/key.result 2012-09-13 08:12:30 +0000
@@ -216,7 +216,7 @@ id select_type table type possible_keys
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
explain select 1 from t1 where id =2 or id=3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
explain select name from t1 where id =2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 NULL
=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result 2012-05-30 11:11:53 +0000
+++ b/mysql-test/r/order_by_all.result 2012-09-13 08:12:30 +0000
@@ -294,7 +294,7 @@ create table t1 (a int not null, b int,
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index
+1 SIMPLE t1 range a a 22 NULL 2 Using where; Using index
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
a b c
1 NULL b
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2012-05-30 11:11:53 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2012-09-13 08:12:30 +0000
@@ -294,7 +294,7 @@ create table t1 (a int not null, b int,
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index
+1 SIMPLE t1 range a a 22 NULL 2 Using where; Using index
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
a b c
1 NULL b
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2012-04-30 10:06:23 +0000
+++ b/mysql-test/r/order_by_none.result 2012-09-13 08:12:30 +0000
@@ -293,7 +293,7 @@ create table t1 (a int not null, b int,
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index a a 22 NULL 11 Using where; Using index
+1 SIMPLE t1 range a a 22 NULL 2 Using where; Using index
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
a b c
1 NULL b
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result 2012-05-22 09:57:00 +0000
+++ b/mysql-test/r/partition_pruning.result 2012-09-13 08:12:30 +0000
@@ -294,7 +294,7 @@ a
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 7;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a > 1;
a
2
@@ -351,13 +351,13 @@ a
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a > 7;
a
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 7;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY)
PARTITION BY RANGE (a) (
@@ -580,7 +580,7 @@ a
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a > 1;
a
2
@@ -625,13 +625,13 @@ a
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
SELECT * FROM t1 WHERE a > 6;
a
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
+1 SIMPLE t1 max range PRIMARY PRIMARY 4 NULL 2 Using where; Using index
DROP TABLE t1;
# test of RANGE and index
CREATE TABLE t1 (a DATE, KEY(a))
@@ -744,10 +744,10 @@ a
1001-01-01
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
@@ -759,10 +759,10 @@ id select_type table partitions type pos
1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 NULL
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index
@@ -781,10 +781,10 @@ id select_type table partitions type pos
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
@@ -793,13 +793,13 @@ id select_type table partitions type pos
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index
+1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 1 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 2 Using where; Using index
# test without index
ALTER TABLE t1 DROP KEY a;
SELECT * FROM t1 WHERE a < '1001-01-01';
@@ -1110,10 +1110,10 @@ id select_type table partitions type pos
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
@@ -1125,7 +1125,7 @@ id select_type table partitions type pos
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 pNULL,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index
@@ -1439,10 +1439,10 @@ id select_type table partitions type pos
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 p2001-01-01,pNULL range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index
@@ -1454,7 +1454,7 @@ id select_type table partitions type pos
1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index
+1 SIMPLE t1 pNULL,p1001-01-01 range a a 4 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 range a a 4 NULL 3 Using where; Using index
=== modified file 'mysql-test/r/partition_range.result'
--- a/mysql-test/r/partition_range.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/partition_range.result 2012-09-13 08:12:30 +0000
@@ -961,24 +961,24 @@ INSERT INTO t2 SELECT * FROM t1;
# plans should be identical
EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index
EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by
+1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index
FLUSH status;
SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
a MAX(b)
10 10
-# Should be no more than 4 reads.
+# Should be no more than 2 reads.
SHOW status LIKE 'handler_read_key';
Variable_name Value
-Handler_read_key 4
+Handler_read_key 2
FLUSH status;
SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
a MAX(b)
10 10
-# Should be no more than 4 reads.
+# Should be no more than 2 reads.
SHOW status LIKE 'handler_read_key';
Variable_name Value
-Handler_read_key 4
+Handler_read_key 2
DROP TABLE t1, t2;
=== modified file 'mysql-test/r/range_all.result'
--- a/mysql-test/r/range_all.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/range_all.result 2012-09-13 08:12:30 +0000
@@ -248,7 +248,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
@@ -1086,10 +1086,10 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (
item char(20) NOT NULL default '',
=== modified file 'mysql-test/r/range_icp.result'
--- a/mysql-test/r/range_icp.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/range_icp.result 2012-09-13 08:12:30 +0000
@@ -248,7 +248,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
@@ -1086,10 +1086,10 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (
item char(20) NOT NULL default '',
=== modified file 'mysql-test/r/range_icp_mrr.result'
--- a/mysql-test/r/range_icp_mrr.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/range_icp_mrr.result 2012-09-13 08:12:30 +0000
@@ -248,7 +248,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
@@ -1086,10 +1086,10 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (
item char(20) NOT NULL default '',
=== modified file 'mysql-test/r/range_mrr.result'
--- a/mysql-test/r/range_mrr.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/range_mrr.result 2012-09-13 08:12:30 +0000
@@ -248,7 +248,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
@@ -1086,10 +1086,10 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (
item char(20) NOT NULL default '',
=== modified file 'mysql-test/r/range_mrr_cost.result'
--- a/mysql-test/r/range_mrr_cost.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/range_mrr_cost.result 2012-09-13 08:12:30 +0000
@@ -248,7 +248,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
@@ -1086,10 +1086,10 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (
item char(20) NOT NULL default '',
=== modified file 'mysql-test/r/range_none.result'
--- a/mysql-test/r/range_none.result 2012-07-31 14:14:52 +0000
+++ b/mysql-test/r/range_none.result 2012-09-13 08:12:30 +0000
@@ -247,7 +247,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref x x 5 const 1 Using index
explain select count(*) from t1 where x in (1,2);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
+1 SIMPLE t1 range x x 5 NULL 2 Using where; Using index
drop table t1;
CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
@@ -1085,10 +1085,10 @@ id b c
0 3 4
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
+1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where
DROP TABLE t1;
CREATE TABLE t1 (
item char(20) NOT NULL default '',
=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result 2012-06-21 02:57:40 +0000
+++ b/mysql-test/r/select_all.result 2012-09-13 08:12:30 +0000
@@ -2197,10 +2197,10 @@ a a
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
a a
1 2
-2 2
-3 2
1 3
+2 2
2 3
+3 2
3 3
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
=== modified file 'mysql-test/r/select_all_bka.result'
--- a/mysql-test/r/select_all_bka.result 2012-06-21 02:57:40 +0000
+++ b/mysql-test/r/select_all_bka.result 2012-09-13 08:12:30 +0000
@@ -2198,10 +2198,10 @@ a a
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
a a
1 2
-2 2
-3 2
1 3
+2 2
2 3
+3 2
3 3
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result 2012-06-21 02:57:40 +0000
+++ b/mysql-test/r/select_icp_mrr.result 2012-09-13 08:12:30 +0000
@@ -2197,10 +2197,10 @@ a a
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
a a
1 2
-2 2
-3 2
1 3
+2 2
2 3
+3 2
3 3
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
=== modified file 'mysql-test/r/select_icp_mrr_bka.result'
--- a/mysql-test/r/select_icp_mrr_bka.result 2012-06-21 02:57:40 +0000
+++ b/mysql-test/r/select_icp_mrr_bka.result 2012-09-13 08:12:30 +0000
@@ -2198,10 +2198,10 @@ a a
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
a a
1 2
-2 2
-3 2
1 3
+2 2
2 3
+3 2
3 3
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
=== modified file 'mysql-test/r/select_none.result'
--- a/mysql-test/r/select_none.result 2012-06-21 02:57:40 +0000
+++ b/mysql-test/r/select_none.result 2012-09-13 08:12:30 +0000
@@ -2196,10 +2196,10 @@ a a
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
a a
1 2
-2 2
-3 2
1 3
+2 2
2 3
+3 2
3 3
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
=== modified file 'mysql-test/r/select_none_bka.result'
--- a/mysql-test/r/select_none_bka.result 2012-06-21 02:57:40 +0000
+++ b/mysql-test/r/select_none_bka.result 2012-09-13 08:12:30 +0000
@@ -2197,10 +2197,10 @@ a a
select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;
a a
1 2
-2 2
-3 2
1 3
+2 2
2 3
+3 2
3 3
select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
a a
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-08-27 11:23:37 +0000
+++ b/mysql-test/r/subquery_all.result 2012-09-13 08:12:30 +0000
@@ -1396,7 +1396,7 @@ Note 1003 /* select#1 */ select `test`.`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
-2 SUBQUERY t2 index s1 s1 6 NULL 2 50.00 Using where; Using index
+2 SUBQUERY t2 range s1 s1 6 NULL 1 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where (`test`.`t2`.`s1` < 'a2') having 1 ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on <auto_key> where ((`test`.`t1`.`s1` = `materialized-subquery`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-08-27 11:23:37 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-09-13 08:12:30 +0000
@@ -1397,7 +1397,7 @@ Note 1003 /* select#1 */ select `test`.`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
-2 SUBQUERY t2 index s1 s1 6 NULL 2 50.00 Using where; Using index
+2 SUBQUERY t2 range s1 s1 6 NULL 1 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where (`test`.`t2`.`s1` < 'a2') having 1 ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on <auto_key> where ((`test`.`t1`.`s1` = `materialized-subquery`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result 2012-08-27 11:23:37 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result 2012-09-13 08:12:30 +0000
@@ -1397,7 +1397,7 @@ Note 1003 /* select#1 */ select `test`.`
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL s1 6 NULL 3 100.00 Using index
-2 SUBQUERY t2 index s1 s1 6 NULL 2 50.00 Using where; Using index
+2 SUBQUERY t2 range s1 s1 6 NULL 1 100.00 Using where; Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,`test`.`t1`.`s1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`s1` from `test`.`t2` where (`test`.`t2`.`s1` < 'a2') having 1 ), <primary_index_lookup>(`test`.`t1`.`s1` in <temporary table> on <auto_key> where ((`test`.`t1`.`s1` = `materialized-subquery`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
drop table t1,t2;
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2012-08-03 09:16:30 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2012-09-13 08:12:30 +0000
@@ -356,7 +356,7 @@ EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
+1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
@@ -380,7 +380,7 @@ INSERT INTO t1(id, dept, age, name) VALU
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by
+1 SIMPLE t1 ref name name 22 const 2 Using where; Using index
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
name dept
rs5 cs10
@@ -389,7 +389,7 @@ DELETE FROM t1;
# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by
+1 SIMPLE t1 ref name name 22 const # Using where; Using index
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
name dept
DROP TABLE t1;
=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2012-08-13 20:13:27 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_all.result 2012-09-13 08:12:30 +0000
@@ -2236,7 +2236,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": false,
"cause": "cost"
}
=== modified file 'mysql-test/suite/opt_trace/r/bugs_no_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result 2012-07-10 11:59:40 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_no_prot_none.result 2012-09-13 08:12:30 +0000
@@ -1697,7 +1697,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": false,
"cause": "cost"
}
=== modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2012-08-13 20:13:27 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_all.result 2012-09-13 08:12:30 +0000
@@ -2236,7 +2236,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": false,
"cause": "cost"
}
=== modified file 'mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result 2012-07-10 11:59:40 +0000
+++ b/mysql-test/suite/opt_trace/r/bugs_ps_prot_none.result 2012-09-13 08:12:30 +0000
@@ -1661,7 +1661,7 @@ SELECT 1 FROM t1 WHERE 1 LIKE
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": false,
"cause": "cost"
}
=== modified file 'mysql-test/suite/opt_trace/r/charset.result'
--- a/mysql-test/suite/opt_trace/r/charset.result 2012-06-05 12:17:53 +0000
+++ b/mysql-test/suite/opt_trace/r/charset.result 2012-09-13 08:12:30 +0000
@@ -431,7 +431,7 @@ select * from t1 where c < 'ÁÂÃÄÅ'
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4313,
"chosen": false,
"cause": "cost"
}
@@ -633,16 +633,28 @@ select `col
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "index\n1\ta",
+ "rows": 1,
+ "ranges": [
+ "NULL < col\n1\ta < 6"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -656,14 +668,14 @@ select `col
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 1,
- "cost": 2.4034,
+ "cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.4034,
+ "cost_for_plan": 1.41,
"rows_for_plan": 1,
"chosen": true
}
@@ -686,7 +698,7 @@ select `col
"refine_plan": [
{
"table": "`t\n1\ta`",
- "access_type": "index_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
=== modified file 'mysql-test/suite/opt_trace/r/eq_range_statistics.result'
--- a/mysql-test/suite/opt_trace/r/eq_range_statistics.result 2012-06-05 12:17:53 +0000
+++ b/mysql-test/suite/opt_trace/r/eq_range_statistics.result 2012-09-13 08:12:30 +0000
@@ -1149,7 +1149,7 @@ EXPLAIN SELECT * FROM t1 WHERE a>5 AND (
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": true
}
] /* range_scan_alternatives */,
@@ -1170,7 +1170,7 @@ EXPLAIN SELECT * FROM t1 WHERE a>5 AND (
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1,
- "cost_for_plan": 2.21,
+ "cost_for_plan": 1.21,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
@@ -1188,12 +1188,12 @@ EXPLAIN SELECT * FROM t1 WHERE a>5 AND (
{
"access_type": "range",
"rows": 1,
- "cost": 2.41,
+ "cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.41,
+ "cost_for_plan": 1.41,
"rows_for_plan": 1,
"chosen": true
}
=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-07-10 11:59:40 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-09-13 08:12:30 +0000
@@ -338,7 +338,7 @@ TRACE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4275,
"chosen": false,
"cause": "cost"
}
@@ -4030,16 +4030,28 @@ select replace(t3._field_140, "\r","^M")
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "rows": 1,
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
},
{
@@ -4117,16 +4129,28 @@ select replace(t3._field_140, "\r","^M")
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "rows": 1,
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
},
{
@@ -4214,16 +4238,28 @@ select replace(t3._field_140, "\r","^M")
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "rows": 1,
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
},
{
@@ -4305,10 +4341,8 @@ select replace(t3._field_140, "\r","^M")
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -4361,10 +4395,8 @@ select replace(t3._field_140, "\r","^M")
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.6,
- "rows": 3,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -4422,10 +4454,8 @@ select replace(t3._field_140, "\r","^M")
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.6,
- "rows": 3,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-07-10 11:59:40 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-09-13 08:12:30 +0000
@@ -366,7 +366,7 @@ TRACE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4275,
"chosen": false,
"cause": "cost"
}
@@ -4075,16 +4075,28 @@ select replace(t3._field_140, "\r","^M")
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "rows": 1,
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
},
{
@@ -4162,16 +4174,28 @@ select replace(t3._field_140, "\r","^M")
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "rows": 1,
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
},
{
@@ -4259,16 +4283,28 @@ select replace(t3._field_140, "\r","^M")
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "PRIMARY",
+ "rows": 1,
+ "ranges": [
+ "1 <= seq_0_id <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
},
{
@@ -4350,10 +4386,8 @@ select replace(t3._field_140, "\r","^M")
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -4406,10 +4440,8 @@ select replace(t3._field_140, "\r","^M")
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.6,
- "rows": 3,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -4467,10 +4499,8 @@ select replace(t3._field_140, "\r","^M")
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.6,
- "rows": 3,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
=== modified file 'mysql-test/suite/opt_trace/r/general_no_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/general_no_prot_all.result 2012-08-21 09:45:53 +0000
+++ b/mysql-test/suite/opt_trace/r/general_no_prot_all.result 2012-09-13 08:12:30 +0000
@@ -1508,16 +1508,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1541,10 +1553,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1575,10 +1585,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1857,16 +1865,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1887,10 +1907,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1930,10 +1948,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -7167,16 +7183,28 @@ insert into t6 select * from t6 where d>
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.4333,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 2,
+ "ranges": [
+ "7 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 2,
+ "cost_for_plan": 1.4333,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -7190,14 +7218,14 @@ insert into t6 select * from t6 where d>
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 2,
- "cost": 2.6051,
+ "cost": 1.8333,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.6051,
+ "cost_for_plan": 1.8333,
"rows_for_plan": 2,
"chosen": true
}
@@ -7220,7 +7248,7 @@ insert into t6 select * from t6 where d>
"refine_plan": [
{
"table": "`t6`",
- "access_type": "index_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
@@ -7555,16 +7583,28 @@ delete t6 from t5, t6 where d>7000 {
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "7000 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -7579,14 +7619,14 @@ delete t6 from t5, t6 where d>7000 {
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 1,
- "cost": 2.8068,
+ "cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.8068,
+ "cost_for_plan": 1.41,
"rows_for_plan": 1,
"chosen": true
}
@@ -7609,7 +7649,7 @@ delete t6 from t5, t6 where d>7000 {
"refine_plan": [
{
"table": "`t6`",
- "access_type": "table_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
=== modified file 'mysql-test/suite/opt_trace/r/general_no_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_no_prot_none.result 2012-08-21 09:45:53 +0000
+++ b/mysql-test/suite/opt_trace/r/general_no_prot_none.result 2012-09-13 08:12:30 +0000
@@ -1551,16 +1551,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1581,10 +1593,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1841,16 +1851,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1871,10 +1893,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -6094,16 +6114,28 @@ insert into t6 select * from t6 where d>
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.4333,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 2,
+ "ranges": [
+ "7 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 2,
+ "cost_for_plan": 1.4333,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -6117,14 +6149,14 @@ insert into t6 select * from t6 where d>
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 2,
- "cost": 2.6051,
+ "cost": 1.8333,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.6051,
+ "cost_for_plan": 1.8333,
"rows_for_plan": 2,
"chosen": true
}
@@ -6147,7 +6179,7 @@ insert into t6 select * from t6 where d>
"refine_plan": [
{
"table": "`t6`",
- "access_type": "index_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
@@ -6482,16 +6514,28 @@ delete t6 from t5, t6 where d>7000 {
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "7000 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -6506,14 +6550,14 @@ delete t6 from t5, t6 where d>7000 {
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 1,
- "cost": 2.8068,
+ "cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.8068,
+ "cost_for_plan": 1.41,
"rows_for_plan": 1,
"chosen": true
}
@@ -6536,7 +6580,7 @@ delete t6 from t5, t6 where d>7000 {
"refine_plan": [
{
"table": "`t6`",
- "access_type": "table_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_all.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_all.result 2012-08-21 09:45:53 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_all.result 2012-09-13 08:12:30 +0000
@@ -1480,16 +1480,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1513,10 +1525,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1547,10 +1557,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1829,16 +1837,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1859,10 +1879,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1902,10 +1920,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -7119,16 +7135,28 @@ insert into t6 select * from t6 where d>
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.4333,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 2,
+ "ranges": [
+ "7 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 2,
+ "cost_for_plan": 1.4333,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -7142,14 +7170,14 @@ insert into t6 select * from t6 where d>
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 2,
- "cost": 2.6051,
+ "cost": 1.8333,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.6051,
+ "cost_for_plan": 1.8333,
"rows_for_plan": 2,
"chosen": true
}
@@ -7172,7 +7200,7 @@ insert into t6 select * from t6 where d>
"refine_plan": [
{
"table": "`t6`",
- "access_type": "index_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
@@ -7507,16 +7535,28 @@ delete t6 from t5, t6 where d>7000 {
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "7000 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -7531,14 +7571,14 @@ delete t6 from t5, t6 where d>7000 {
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 1,
- "cost": 2.8068,
+ "cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.8068,
+ "cost_for_plan": 1.41,
"rows_for_plan": 1,
"chosen": true
}
@@ -7561,7 +7601,7 @@ delete t6 from t5, t6 where d>7000 {
"refine_plan": [
{
"table": "`t6`",
- "access_type": "table_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
=== modified file 'mysql-test/suite/opt_trace/r/general_ps_prot_none.result'
--- a/mysql-test/suite/opt_trace/r/general_ps_prot_none.result 2012-08-21 09:45:53 +0000
+++ b/mysql-test/suite/opt_trace/r/general_ps_prot_none.result 2012-09-13 08:12:30 +0000
@@ -1523,16 +1523,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1553,10 +1565,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -1813,16 +1823,28 @@ explain SELECT c FROM t5 where c+1 in (s
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "NULL <= d <= NULL"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -1843,10 +1865,8 @@ explain SELECT c FROM t5 where c+1 in (s
"chosen": true
},
{
- "access_type": "scan",
- "cost": 2.4,
- "rows": 2,
- "cause": "cost",
+ "access_type": "range",
+ "cause": "heuristic_index_cheaper",
"chosen": false
}
] /* considered_access_paths */
@@ -6028,16 +6048,28 @@ insert into t6 select * from t6 where d>
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.4333,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 2,
+ "ranges": [
+ "7 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 2,
+ "cost_for_plan": 1.4333,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -6051,14 +6083,14 @@ insert into t6 select * from t6 where d>
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 2,
- "cost": 2.6051,
+ "cost": 1.8333,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.6051,
+ "cost_for_plan": 1.8333,
"rows_for_plan": 2,
"chosen": true
}
@@ -6081,7 +6113,7 @@ insert into t6 select * from t6 where d>
"refine_plan": [
{
"table": "`t6`",
- "access_type": "index_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
@@ -6416,16 +6448,28 @@ delete t6 from t5, t6 where d>7000 {
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "d",
+ "rows": 1,
+ "ranges": [
+ "7000 < d"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -6440,14 +6484,14 @@ delete t6 from t5, t6 where d>7000 {
"best_access_path": {
"considered_access_paths": [
{
- "access_type": "scan",
+ "access_type": "range",
"rows": 1,
- "cost": 2.8068,
+ "cost": 1.41,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 2.8068,
+ "cost_for_plan": 1.41,
"rows_for_plan": 1,
"chosen": true
}
@@ -6470,7 +6514,7 @@ delete t6 from t5, t6 where d>7000 {
"refine_plan": [
{
"table": "`t6`",
- "access_type": "table_scan"
+ "access_type": "range"
}
] /* refine_plan */
}
=== modified file 'mysql-test/suite/opt_trace/r/range_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_no_prot.result 2012-08-23 08:01:05 +0000
+++ b/mysql-test/suite/opt_trace/r/range_no_prot.result 2012-09-13 08:12:30 +0000
@@ -1797,12 +1797,12 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": true
}
] /* range_scan_alternatives */,
"index_to_merge": "i2",
- "cumulated_cost": 2.21
+ "cumulated_cost": 1.21
},
{
"range_scan_alternatives": [
@@ -1816,15 +1816,15 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": true
}
] /* range_scan_alternatives */,
"index_to_merge": "i3",
- "cumulated_cost": 4.42
+ "cumulated_cost": 2.42
}
] /* indices_to_merge */,
- "cost_of_reading_ranges": 4.42,
+ "cost_of_reading_ranges": 2.42,
"use_roworder_union": true,
"cause": "always_cheaper_than_not_roworder_retrieval",
"analyzing_roworder_scans": [
@@ -1853,7 +1853,7 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
} /* analyzing_roworder_intersect */
}
] /* analyzing_roworder_scans */,
- "index_roworder_union_cost": 6.327,
+ "index_roworder_union_cost": 4.327,
"members": 2,
"chosen": true
}
@@ -1881,7 +1881,7 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
] /* union_of */
} /* range_access_plan */,
"rows_for_plan": 2,
- "cost_for_plan": 6.327,
+ "cost_for_plan": 4.327,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
@@ -1899,12 +1899,12 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
{
"access_type": "range",
"rows": 2,
- "cost": 6.727,
+ "cost": 4.727,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 6.727,
+ "cost_for_plan": 4.727,
"rows_for_plan": 2,
"chosen": true
}
@@ -4835,7 +4835,7 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4333,
"chosen": false,
"cause": "cost"
},
@@ -4849,7 +4849,7 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4423,
"chosen": false,
"cause": "cost"
}
@@ -4961,7 +4961,7 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4333,
"chosen": false,
"cause": "cost"
}
@@ -5204,9 +5204,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
@@ -5230,7 +5229,20 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"chosen": false,
"cause": "too_few_indexes_to_merge"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "v_idx",
+ "rows": 1,
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -5258,10 +5270,9 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"chosen": false
},
{
- "access_type": "scan",
- "cost": 2,
- "rows": 5,
- "cause": "cost",
+ "access_type": "range",
+ "rows": 1,
+ "cost": 1.41,
"chosen": false
}
] /* considered_access_paths */
@@ -5775,7 +5786,7 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4115,
"chosen": false,
"cause": "cost"
}
=== modified file 'mysql-test/suite/opt_trace/r/range_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/range_ps_prot.result 2012-08-23 08:01:05 +0000
+++ b/mysql-test/suite/opt_trace/r/range_ps_prot.result 2012-09-13 08:12:30 +0000
@@ -1797,12 +1797,12 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": true
}
] /* range_scan_alternatives */,
"index_to_merge": "i2",
- "cumulated_cost": 2.21
+ "cumulated_cost": 1.21
},
{
"range_scan_alternatives": [
@@ -1816,15 +1816,15 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
+ "cost": 1.21,
"chosen": true
}
] /* range_scan_alternatives */,
"index_to_merge": "i3",
- "cumulated_cost": 4.42
+ "cumulated_cost": 2.42
}
] /* indices_to_merge */,
- "cost_of_reading_ranges": 4.42,
+ "cost_of_reading_ranges": 2.42,
"use_roworder_union": true,
"cause": "always_cheaper_than_not_roworder_retrieval",
"analyzing_roworder_scans": [
@@ -1853,7 +1853,7 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
} /* analyzing_roworder_intersect */
}
] /* analyzing_roworder_scans */,
- "index_roworder_union_cost": 6.327,
+ "index_roworder_union_cost": 4.327,
"members": 2,
"chosen": true
}
@@ -1881,7 +1881,7 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
] /* union_of */
} /* range_access_plan */,
"rows_for_plan": 2,
- "cost_for_plan": 6.327,
+ "cost_for_plan": 4.327,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
@@ -1899,12 +1899,12 @@ EXPLAIN SELECT * FROM t1 WHERE key2=10 O
{
"access_type": "range",
"rows": 2,
- "cost": 6.727,
+ "cost": 4.727,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
- "cost_for_plan": 6.727,
+ "cost_for_plan": 4.727,
"rows_for_plan": 2,
"chosen": true
}
@@ -4835,7 +4835,7 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4333,
"chosen": false,
"cause": "cost"
},
@@ -4849,7 +4849,7 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4423,
"chosen": false,
"cause": "cost"
}
@@ -4961,7 +4961,7 @@ EXPLAIN SELECT DISTINCT i1 FROM t1 WHERE
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4333,
"chosen": false,
"cause": "cost"
}
@@ -5204,9 +5204,8 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"using_mrr": false,
"index_only": true,
"rows": 1,
- "cost": 2.21,
- "chosen": false,
- "cause": "cost"
+ "cost": 1.21,
+ "chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
@@ -5230,7 +5229,20 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"chosen": false,
"cause": "too_few_indexes_to_merge"
} /* analyzing_roworder_intersect */
- } /* analyzing_range_alternatives */
+ } /* analyzing_range_alternatives */,
+ "chosen_range_access_summary": {
+ "range_access_plan": {
+ "type": "range_scan",
+ "index": "v_idx",
+ "rows": 1,
+ "ranges": [
+ "a <= v <= a AND 1 <= i1 <= 1"
+ ] /* ranges */
+ } /* range_access_plan */,
+ "rows_for_plan": 1,
+ "cost_for_plan": 1.21,
+ "chosen": true
+ } /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
@@ -5258,10 +5270,9 @@ EXPLAIN SELECT v FROM t1 WHERE i1 = 1 AN
"chosen": false
},
{
- "access_type": "scan",
- "cost": 2,
- "rows": 5,
- "cause": "cost",
+ "access_type": "range",
+ "rows": 1,
+ "cost": 1.41,
"chosen": false
}
] /* considered_access_paths */
@@ -5775,7 +5786,7 @@ EXPLAIN SELECT MAX(b), a FROM t1 WHERE b
"using_mrr": false,
"index_only": true,
"rows": 2,
- "cost": 3.41,
+ "cost": 1.4115,
"chosen": false,
"cause": "cost"
}
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2012-08-23 07:45:33 +0000
+++ b/mysql-test/t/group_by.test 2012-09-13 08:12:30 +0000
@@ -1979,6 +1979,8 @@ CREATE TABLE t1 (
);
INSERT INTO t1 VALUES (1,NULL),(0,'a'),(1,NULL),(0,'a');
+INSERT INTO t1 VALUES (1,'a'),(0,'a'),(1,'a'),(0,'a');
+ANALYZE TABLE t1;
let $query=
SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test 2012-08-23 07:45:33 +0000
+++ b/mysql-test/t/group_min_max.test 2012-09-13 08:12:30 +0000
@@ -825,12 +825,16 @@ DROP TABLE t1,t2,t3,t4,t5,t6;
#
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+INSERT INTO t1 VALUES (2,1),(2,2),(2,0),(2,3);
+INSERT INTO t1 VALUES (3,1),(3,2),(3,0),(3,3);
+ANALYZE TABLE t1;
explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
INSERT INTO t2 SELECT a,b,b FROM t1;
+ANALYZE TABLE t2;
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
=== modified file 'mysql-test/t/partition_range.test'
--- a/mysql-test/t/partition_range.test 2011-01-10 15:13:12 +0000
+++ b/mysql-test/t/partition_range.test 2012-09-13 08:12:30 +0000
@@ -963,12 +963,12 @@ EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a
FLUSH status;
SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a;
---echo # Should be no more than 4 reads.
+--echo # Should be no more than 2 reads.
SHOW status LIKE 'handler_read_key';
FLUSH status;
SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a;
---echo # Should be no more than 4 reads.
+--echo # Should be no more than 2 reads.
SHOW status LIKE 'handler_read_key';
DROP TABLE t1, t2;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2012-08-31 10:11:32 +0000
+++ b/sql/handler.cc 2012-09-13 08:12:30 +0000
@@ -5551,7 +5551,7 @@ handler::multi_range_read_info_const(uin
*flags|= HA_MRR_SUPPORT_SORTED;
DBUG_ASSERT(cost->is_zero());
- if ((*flags & HA_MRR_INDEX_ONLY) && total_rows > 2)
+ if (*flags & HA_MRR_INDEX_ONLY)
cost->add_io(index_only_read_time(keyno, total_rows) *
Cost_estimate::IO_BLOCK_READ_COST());
else
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:4467 to 4468) Bug#14095506 | Jorgen Loland | 13 Sep |
