List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:March 4 2010 11:09am
Subject:bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3319)
Bug#48229
View as plain text  
#At file:///Users/mattiasj/clones/bzrroot/b48229-51-bugteam/ based on revid:mattias.jonsson@stripped

 3319 Mattias Jonsson	2010-03-04
      Bug#48229: group by performance issue of partitioned table
      
      Problem was block_size on partitioned tables was not set,
      resulting in keys_per_block was not correct which affects
      the cost calculation for read time of indexes (including
      cost for group min/max).Which resulted in a 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-03-04 11:09:09 +0000
@@ -1,4 +1,18 @@
 drop table if exists t1, t2;
+#
+# Bug#48229: group by performance issue of partitioned table
+#
+CREATE TABLE t1 (
+a INT,
+b INT,
+KEY a (a,b)
+)
+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-03-04 11:09:09 +0000
@@ -9,6 +9,24 @@
 drop table if exists t1, t2;
 --enable_warnings
 
+--echo #
+--echo # Bug#48229: group by performance issue of partitioned table
+--echo #
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ KEY a (a,b)
+)
+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
 #

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2009-11-17 19:02:16 +0000
+++ b/sql/ha_partition.cc	2010-03-04 11:09:09 +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]
Thread
bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3319)Bug#48229Mattias Jonsson4 Mar