#At file:///home/mysql_src/bzrrepos/mysql-next-mr-bugfixing/ based on revid:guilhem@stripped
2887 Guilhem Bichot 2009-10-05
Backport of the fix for BUG#33770 "Full table scan instead selected partitions for query more than 10 partitions"
from 6.0, made in sergefp@stripped
modified:
mysql-test/r/partition_hash.result
mysql-test/r/partition_pruning.result
mysql-test/t/partition_pruning.test
sql/sql_partition.cc
=== modified file 'mysql-test/r/partition_hash.result'
--- a/mysql-test/r/partition_hash.result 2008-07-07 20:42:19 +0000
+++ b/mysql-test/r/partition_hash.result 2009-10-05 20:51:41 +0000
@@ -93,7 +93,7 @@ id select_type table partitions type pos
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
explain partitions select * from t1 where a >= 1 and a <= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 9 Using where
drop table t1;
CREATE TABLE t1 (
a int not null,
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result 2009-09-01 12:53:27 +0000
+++ b/mysql-test/r/partition_pruning.result 2009-10-05 20:51:41 +0000
@@ -2229,3 +2229,22 @@ explain partitions select * from t1 wher
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
drop table t1;
+#
+# BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int)
+partition by range(a+0) (
+partition p0 values less than (64),
+partition p1 values less than (128),
+partition p2 values less than (255)
+);
+insert into t1 select A.a + 10*B.a from t0 A, t0 B;
+explain partitions select * from t1 where a between 10 and 13;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 64 Using where
+explain partitions select * from t1 where a between 10 and 10+33;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 100 Using where
+drop table t0, t1;
=== modified file 'mysql-test/t/partition_pruning.test'
--- a/mysql-test/t/partition_pruning.test 2009-08-28 10:55:59 +0000
+++ b/mysql-test/t/partition_pruning.test 2009-10-05 20:51:41 +0000
@@ -1159,3 +1159,22 @@ INSERT INTO t1 VALUES ('2006-03-01 12:00
-- echo must use p0 only:
explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
drop table t1;
+
+-- echo #
+-- echo # BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
+-- echo #
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int)
+ partition by range(a+0) (
+ partition p0 values less than (64),
+ partition p1 values less than (128),
+ partition p2 values less than (255)
+);
+insert into t1 select A.a + 10*B.a from t0 A, t0 B;
+
+# this will use interval_via_walking
+explain partitions select * from t1 where a between 10 and 13;
+explain partitions select * from t1 where a between 10 and 10+33;
+
+drop table t0, t1;
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2009-09-28 07:39:50 +0000
+++ b/sql/sql_partition.cc 2009-10-05 20:51:41 +0000
@@ -6850,7 +6850,7 @@ int get_part_iter_for_interval_via_mappi
/* See get_part_iter_for_interval_via_walking for definition of what this is */
-#define MAX_RANGE_TO_WALK 10
+#define MAX_RANGE_TO_WALK 32
/*
@@ -6886,16 +6886,6 @@ int get_part_iter_for_interval_via_mappi
Intervals with +inf/-inf, and [NULL, c1] interval can be processed but
that is more tricky and I don't have time to do it right now.
- Additionally we have these requirements:
- * number of values in the interval must be less then number of
- [sub]partitions, and
- * Number of values in the interval must be less then MAX_RANGE_TO_WALK.
-
- The rationale behind these requirements is that if they are not met
- we're likely to hit most of the partitions and traversing the interval
- will only add overhead. So it's better return "all partitions used" in
- that case.
-
RETURN
0 - No matching partitions, iterator not initialized
1 - Some partitions would match, iterator intialized for traversing them
@@ -6989,8 +6979,24 @@ int get_part_iter_for_interval_via_walki
a += test(flags & NEAR_MIN);
b += test(!(flags & NEAR_MAX));
ulonglong n_values= b - a;
-
- if (n_values > total_parts || n_values > MAX_RANGE_TO_WALK)
+
+ /*
+ Will it pay off to enumerate all values in the [a..b] range and evaluate
+ the partitioning function for every value? It depends on
+ 1. whether we'll be able to infer that some partitions are not used
+ 2. if time savings from not scanning these partitions will be greater
+ than time spent in enumeration.
+ We will assume that the cost of accessing one extra partition is greater
+ than the cost of evaluating the partitioning function O(#partitions).
+ This means we should jump at any chance to eliminate a partition, which
+ gives us this logic:
+
+ Do the enumeration if
+ - the number of values to enumerate is comparable to the number of
+ partitions, or
+ - there are not many values to enumerate.
+ */
+ if ((n_values > 2*total_parts) && n_values > MAX_RANGE_TO_WALK)
return -1;
part_iter->field_vals.start= part_iter->field_vals.cur= a;
Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20091005205141-7aq4wodp3ngq1dwf.bundle
Thread |
---|
• bzr commit into mysql-5.4.5-next-mr branch (guilhem:2887) Bug#33770 | Guilhem Bichot | 5 Oct |