#At file:///Users/mattiasj/clones/bzrroot/b48229-51-bugteam/ based on revid:mattias.jonsson@stripped
3319 Mattias Jonsson 2010-02-04
Bug#48229: group by performance issue of partitioned table
Problem was block_size on partitioned tables was not set,
resulting in bad optimizer decision.
Fixed by setting stats.block_size correctly.
@ mysql-test/r/partition_range.result
Bug#48229: group by performance issue of partitioned table
Added result
@ mysql-test/t/partition_range.test
Bug#48229: group by performance issue of partitioned table
Added test
@ sql/ha_partition.cc
Bug#48229: group by performance issue of partitioned table
Added missing assignment of stats.block_size.
modified:
mysql-test/r/partition_range.result
mysql-test/t/partition_range.test
sql/ha_partition.cc
=== modified file 'mysql-test/r/partition_range.result'
--- a/mysql-test/r/partition_range.result 2009-08-26 10:59:49 +0000
+++ b/mysql-test/r/partition_range.result 2010-02-04 18:11:50 +0000
@@ -1,4 +1,15 @@
drop table if exists t1, t2;
+CREATE TABLE `t1` (
+`a` int(11) DEFAULT NULL,
+`b` int(11) DEFAULT NULL,
+KEY `a` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+PARTITION BY HASH (a) PARTITIONS 1;
+INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020);
+EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index
+DROP TABLE t1;
create table t1 (a int)
partition by range (a)
( partition p0 values less than (maxvalue));
=== modified file 'mysql-test/t/partition_range.test'
--- a/mysql-test/t/partition_range.test 2008-09-18 19:49:34 +0000
+++ b/mysql-test/t/partition_range.test 2010-02-04 18:11:50 +0000
@@ -10,6 +10,24 @@ drop table if exists t1, t2;
--enable_warnings
#
+# Bug#48229: group by performance issue of partitioned table
+#
+CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL,
+ KEY `a` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+PARTITION BY HASH (a) PARTITIONS 1;
+
+# insert some rows (i.e. so that rows/blocks > 1)
+INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020);
+
+# Before the fix the 'Extra' column showed 'Using index for group-by'
+EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a;
+
+DROP TABLE t1;
+
+#
# BUG 33429: Succeeds in adding partition when maxvalue on last partition
#
create table t1 (a int)
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2009-11-17 19:02:16 +0000
+++ b/sql/ha_partition.cc 2010-02-04 18:11:50 +0000
@@ -5053,6 +5053,7 @@ int ha_partition::info(uint flag)
file= m_file[handler_instance];
file->info(HA_STATUS_CONST);
+ stats.block_size= file->stats.block_size;
stats.create_time= file->stats.create_time;
ref_length= m_ref_length;
}
Attachment: [text/bzr-bundle]
