List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:October 8 2009 1:58pm
Subject:bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3152)
Bug#44059
View as plain text  
#At file:///Users/mattiasj/clones/bzrroot/b44059-51-bugteam/ based on revid:joro@stripped

 3152 Mattias Jonsson	2009-10-08
      Bug#44059: Incorrect cardinality of indexes on a partitioned table
      
      backport for bug#44059 from mysql-pe to mysql-5.1-bugteam
      
      Using the partition with most rows instead of first partition
      to estimate the cardinality of indexes.
     @ mysql-test/r/partition.result
        Bug#44059: Incorrect cardinality of indexes on a partitioned table
        
        Added test result
     @ mysql-test/t/partition.test
        Bug#44059: Incorrect cardinality of indexes on a partitioned table
        
        Added test case
     @ sql/ha_partition.cc
        Bug#44059: Incorrect cardinality of indexes on a partitioned table
        
        Checking which partition that has the most rows, and using that
        partition for HA_STATUS_CONST instead of first partition

    modified:
      mysql-test/r/partition.result
      mysql-test/t/partition.test
      sql/ha_partition.cc
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result	2009-08-21 15:38:29 +0000
+++ b/mysql-test/r/partition.result	2009-10-08 13:58:17 +0000
@@ -50,6 +50,21 @@ t1	CREATE TABLE `t1` (
  PARTITION p3 VALUES LESS THAN (733969) ENGINE = MyISAM,
  PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
 DROP TABLE t1;
+create table t1 (a int, b int, key(a))
+partition by list (a)
+( partition p0 values in (1),
+partition p1 values in (2));
+insert into t1 values (1,1),(2,1),(2,2),(2,3);
+show indexes from t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE	
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+show indexes from t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+t1	1	a	1	a	A	1	NULL	NULL	YES	BTREE	
+drop table t1;
 CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
 ENGINE=MyISAM
 PARTITION BY HASH (a);

=== modified file 'mysql-test/t/partition.test'
--- a/mysql-test/t/partition.test	2009-08-21 15:38:29 +0000
+++ b/mysql-test/t/partition.test	2009-10-08 13:58:17 +0000
@@ -62,6 +62,19 @@ SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 #
+# Bug#44059: rec_per_key on empty partition gives weird optimiser results
+#
+create table t1 (a int, b int, key(a))
+partition by list (a)
+( partition p0 values in (1),
+  partition p1 values in (2));
+insert into t1 values (1,1),(2,1),(2,2),(2,3);
+show indexes from t1;
+analyze table t1;
+show indexes from t1;
+drop table t1;
+
+#
 # Bug#36001: Partitions: spelling and using some error messages
 #
 --error ER_FOREIGN_KEY_ON_PARTITIONED

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2009-09-23 13:21:29 +0000
+++ b/sql/ha_partition.cc	2009-10-08 13:58:17 +0000
@@ -5011,8 +5011,9 @@ int ha_partition::info(uint flag)
       If the handler doesn't support statistics, it should set all of the
       above to 0.
 
-      We will allow the first handler to set the rec_per_key and use
-      this as an estimate on the total table.
+      We first scans through all partitions to get the one holding most rows.
+      We will then allow the handler with the most rows to set
+      the rec_per_key and use this as an estimate on the total table.
 
       max_data_file_length:     Maximum data file length
       We ignore it, is only used in
@@ -5024,14 +5025,33 @@ int ha_partition::info(uint flag)
       ref_length:               We set this to the value calculated
       and stored in local object
       create_time:              Creation time of table
-      Set by first handler
 
-      So we calculate these constants by using the variables on the first
-      handler.
+      So we calculate these constants by using the variables from the
+      handler with most rows.
     */
-    handler *file;
+    handler *file, **file_array;
+    ulonglong max_records= 0;
+    uint32 i= 0;
+    uint32 handler_instance= 0;
+
+    file_array= m_file;
+    do
+    {
+      file= *file_array;
+      /* Get variables if not already done */
+      if (!(flag & HA_STATUS_VARIABLE) ||
+          !bitmap_is_set(&(m_part_info->used_partitions),
+                         (file_array - m_file)))
+        file->info(HA_STATUS_VARIABLE);
+      if (file->stats.records > max_records)
+      {
+        max_records= file->stats.records;
+        handler_instance= i;
+      }
+      i++;
+    } while (*(++file_array));
 
-    file= m_file[0];
+    file= m_file[handler_instance];
     file->info(HA_STATUS_CONST);
     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:3152)Bug#44059Mattias Jonsson8 Oct