Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-06-15 13:55:18+03:00, mhansson@stripped +3 -0
bug #26106: Wrong plan may be chosen when there are several possible range and ref
accesses
For ref(const) access methods there are two kinds of statistics when a prefix of a
key is matched: range access estimate and table statistics, which are not necessarily
consistent with each other. This may make the optimizer choose a key which matches
fewer key parts than some other key, which is obviously not correct.
Fixed by giving preferrence to the keys that match the most parts, and choosing the
best one among these.
mysql-test/r/range.result@stripped, 2007-06-15 13:55:12+03:00, mhansson@stripped +57 -0
bug #26106: test result
mysql-test/t/range.test@stripped, 2007-06-15 13:55:12+03:00, mhansson@stripped +58 -0
bug #26106: test case
sql/sql_select.cc@stripped, 2007-06-15 13:55:12+03:00, mhansson@stripped +21 -2
bug #26106: If it is discovered that one or more range estimates match more
key parts than all known ref estimates, we will look for and use the best range
estimate.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mhansson
# Host: linux-st28.site
# Root: /home/martin/mysql/src/5.1o-bug26106
--- 1.497/sql/sql_select.cc 2007-02-27 10:39:02 +02:00
+++ 1.498/sql/sql_select.cc 2007-06-15 13:55:12 +03:00
@@ -3778,6 +3778,13 @@ best_access_path(JOIN *join,
double best= DBL_MAX;
double best_time= DBL_MAX;
double records= DBL_MAX;
+ /* Holds the current number of records from the range optimizer, if any */
+ double quick_records= DBL_MAX;
+ /*
+ When a key from the range optimizer matches more parts than a ref key, the
+ estimates from these sources are not comparable.
+ */
+ double best_quick_records= DBL_MAX;
table_map best_ref_depends_map= 0;
double tmp;
ha_rows rec;
@@ -3807,6 +3814,12 @@ best_access_path(JOIN *join,
/* Calculate how many key segments of the current key we can use */
start_key= keyuse;
+ /*
+ True if we find some keys from the range optimizer that match more
+ key parts than the best ref key. We then choose the best range key.
+ */
+ bool quick_matches_more_parts= false;
+
do /* For each keypart */
{
uint keypart= keyuse->keypart;
@@ -4040,7 +4053,10 @@ best_access_path(JOIN *join,
if (!found_ref && table->quick_keys.is_set(key) && // (1)
table->quick_key_parts[key] > max_key_part && // (2)
records < (double)table->quick_rows[key]) // (3)
- records= (double)table->quick_rows[key];
+ {
+ records= quick_records= (double)table->quick_rows[key];
+ quick_matches_more_parts= true;
+ }
tmp= records;
}
@@ -4129,8 +4145,11 @@ best_access_path(JOIN *join,
tmp= best_time; // Do nothing
}
} /* not ft_key */
- if (tmp < best_time - records/(double) TIME_FOR_COMPARE)
+ if (tmp < best_time - records/(double) TIME_FOR_COMPARE ||
+ (quick_matches_more_parts &&
+ quick_records < best_quick_records))
{
+ best_quick_records = quick_records;
best_time= tmp + records/(double) TIME_FOR_COMPARE;
best= tmp;
best_records= records;
--- 1.59/mysql-test/r/range.result 2007-02-06 16:52:22 +02:00
+++ 1.60/mysql-test/r/range.result 2007-06-15 13:55:12 +03:00
@@ -1008,3 +1008,60 @@ explain select * from t2 where a=1000 an
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 5 const 502 Using where
drop table t1, t2;
+CREATE TABLE t1(
+a INT,
+b INT,
+KEY k ( a ),
+KEY l ( a, b )
+);
+INSERT INTO t1(a) VALUES (1);
+INSERT INTO t1
+VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3);
+INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3;
+INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+INSERT INTO t1 VALUES (1, 2);
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+This must use range over index l, not k.
+EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range k,l l 10 NULL 138 Using where; Using index
+CREATE TABLE t2(
+a INT,
+b INT,
+c INT,
+KEY k ( a ),
+KEY l ( a, b ),
+KEY m ( b ),
+KEY n ( a, c )
+);
+INSERT INTO t2(a) VALUES (1);
+INSERT INTO t2
+VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3),
+(2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3);
+INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3;
+INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t2;
+Table Op Msg_type Msg_text
+test.t2 analyze status OK
+INSERT INTO t2 VALUES (1, 2, 2);
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 VALUES (1, 1, 2);
+This must use range over index l, not n.
+EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref k,l,m,n l 5 const 69 Using where
+DROP TABLE t1, t2;
--- 1.47/mysql-test/t/range.test 2007-02-06 16:52:22 +02:00
+++ 1.48/mysql-test/t/range.test 2007-06-15 13:55:12 +03:00
@@ -820,3 +820,61 @@ explain select * from t2 where a=1000 an
drop table t1, t2;
+#
+# Bug #26106: Wrong plan may be chosen when there are several possible range and
+# ref accesses
+#
+CREATE TABLE t1(
+ a INT,
+ b INT,
+ KEY k ( a ),
+ KEY l ( a, b )
+);
+
+INSERT INTO t1(a) VALUES (1);
+INSERT INTO t1
+VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3);
+INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3;
+INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t1;
+INSERT INTO t1 VALUES (1, 2);
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 2
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 4
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 8
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 16
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 32
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 64
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 128
+--echo This must use range over index l, not k.
+EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2;
+
+CREATE TABLE t2(
+ a INT,
+ b INT,
+ c INT,
+ KEY k ( a ),
+ KEY l ( a, b ),
+ KEY m ( b ),
+ KEY n ( a, c )
+);
+
+INSERT INTO t2(a) VALUES (1);
+INSERT INTO t2
+VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3),
+ (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3);
+INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3;
+INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t2;
+INSERT INTO t2 VALUES (1, 2, 2);
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 2
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 4
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 8
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 16
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 32
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 64
+INSERT INTO t2 VALUES (1, 1, 2);
+
+--echo This must use range over index l, not n.
+EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2;
+
+DROP TABLE t1, t2;
| Thread |
|---|
| • bk commit into 5.1 tree (mhansson:1.2466) BUG#26106 | mhansson | 15 Jun |
