[Commits] Rev 4157: Merge. in file:///home/igor/maria/maria-10.0/

Igor Babaev igor at askmonty.org
Wed Apr 23 00:43:15 EEST 2014


At file:///home/igor/maria/maria-10.0/

------------------------------------------------------------
revno: 4157 [merge]
revision-id: igor at askmonty.org-20140422214313-9bge1seigkxt8z2l
parent: bar at mnogosearch.org-20140422072335-mwqnu9qm397ieshv
parent: igor at askmonty.org-20140422213957-hwicqzam2aenhfnm
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-10.0
timestamp: Tue 2014-04-22 14:43:13 -0700
message:
  Merge.
modified:
  mysql-test/r/distinct.result   sp1f-distinct.result-20001228015633-adu7puhxwf4tiwor5amegcrjobuxljra
  mysql-test/r/func_group_innodb.result sp1f-func_group_innodb.re-20060522112752-jzcl2uuvlbohttdur3oawnh7jxxdhl4w
  mysql-test/r/group_by.result   sp1f-group_by.result-20001228015633-bgjibbiwynctdjq73ms5muj5g6hfpv4d
  mysql-test/r/group_min_max.result sp1f-group_min_max.result-20040827133611-aqzadxttbw23mkanmvdsiaambv2pcy27
  mysql-test/t/func_group_innodb.test sp1f-func_group_innodb.te-20060522112752-qpwhp3dyn42s7cjdrhfsuo6myr6xampi
  mysql-test/t/group_by.test     sp1f-group_by.test-20001228015636-5zwfyi7n5e5jsbyby7a7fvitybcefqwu
  mysql-test/t/group_min_max.test sp1f-group_min_max.test-20040827133612-bbe7hj6l7byvtyxsg4iicylzflsgy6vj
  sql/opt_range.cc               sp1f-opt_range.cc-19700101030959-afe3wtevb7zwrg4xyibt35uamov5r7ds
-------------- next part --------------
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2014-02-25 15:04:35 +0000
+++ b/mysql-test/r/distinct.result	2014-04-22 21:39:57 +0000
@@ -756,7 +756,7 @@
 (1, 2, 3);
 EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	a	16	NULL	7	Using index for group-by
+1	SIMPLE	t1	index	NULL	a	16	NULL	6	Using index
 SELECT DISTINCT a, b, d, c FROM t1;
 a	b	d	c
 1	1	0	1

=== modified file 'mysql-test/r/func_group_innodb.result'
--- a/mysql-test/r/func_group_innodb.result	2013-09-14 01:09:36 +0000
+++ b/mysql-test/r/func_group_innodb.result	2014-04-22 21:39:57 +0000
@@ -236,12 +236,14 @@
 #
 CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT,
 KEY(b, c, a(765))) ENGINE=INNODB;
-INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
+INSERT INTO t1(a, b, c) VALUES 
+('', 'a', 0), ('', 'a', null), ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
+ANALYZE TABLE t1;
 SELECT MIN(c) FROM t1 GROUP BY b;
 MIN(c)
 0
 EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	b	263	NULL	4	Using index for group-by
+1	SIMPLE	t1	range	NULL	b	263	NULL	3	Using index for group-by
 DROP TABLE t1;
 End of 5.5 tests

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2014-02-03 14:22:39 +0000
+++ b/mysql-test/r/group_by.result	2014-04-22 21:39:57 +0000
@@ -1524,7 +1524,7 @@
 EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 
 FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	i2	4	NULL	145	Using index for group-by
+1	SIMPLE	t1	index	NULL	i2	9	NULL	144	Using index
 EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	144	
@@ -1957,12 +1957,12 @@
 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
 EXPLAIN SELECT col1 AS field1, col1 AS field2
-FROM t1 GROUP BY field1, field2+0;;
+FROM t1 GROUP BY field1, field2;;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx	5	NULL	20	Using index; Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	idx	5	NULL	20	Using index
 FLUSH STATUS;
 SELECT col1 AS field1, col1 AS field2
-FROM t1 GROUP BY field1, field2+0;;
+FROM t1 GROUP BY field1, field2;;
 field1	field2
 1	1
 2	2
@@ -1986,7 +1986,7 @@
 20	20
 SHOW SESSION STATUS LIKE 'Sort_scan%';
 Variable_name	Value
-Sort_scan	1
+Sort_scan	0
 EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
 FROM t1 GROUP BY field1, field2;;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2320,7 +2320,7 @@
 b varchar(1),
 KEY (b,a)
 );
-INSERT INTO t1 VALUES (1,NULL),(0,'a');
+INSERT INTO t1 VALUES (1,NULL),(0,'a'),(1,NULL),(0,'a');
 
 EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2013-11-20 11:05:39 +0000
+++ b/mysql-test/r/group_min_max.result	2014-04-22 21:39:57 +0000
@@ -1,6 +1,6 @@
 drop table if exists t1;
 create table t1 (
-a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
+a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
 );
 insert into t1 (a1, a2, b, c, d) values
 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
@@ -43,7 +43,7 @@
 test.t1	analyze	status	Table is already up to date
 drop table if exists t2;
 create table t2 (
-a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
+a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
 );
 insert into t2 select * from t1;
 insert into t2 (a1, a2, b, c, d) values
@@ -2649,6 +2649,7 @@
 CREATE TABLE t (a INT, b INT, INDEX (a,b));
 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
 INSERT INTO t SELECT * FROM t;
+INSERT INTO t SELECT * FROM t;
 # test MIN
 #should use range with index for group by
 EXPLAIN
@@ -3281,6 +3282,7 @@
 #
 CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
 INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
 CREATE TABLE t2 (c int) ;
 INSERT INTO t2 VALUES (0),(1);
 EXPLAIN
@@ -3303,10 +3305,10 @@
 EXPLAIN
 SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	b	10	NULL	9	Using where; Using index
+1	PRIMARY	t1	index	NULL	b	10	NULL	18	Using where; Using index
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	SUBQUERY	t1a	index	NULL	b	10	NULL	9	Using index; Using join buffer (flat, BNL join)
-2	SUBQUERY	t1b	index	NULL	b	10	NULL	9	Using index; Using join buffer (incremental, BNL join)
+2	SUBQUERY	t1a	index	NULL	b	10	NULL	18	Using index; Using join buffer (flat, BNL join)
+2	SUBQUERY	t1b	index	NULL	b	10	NULL	18	Using index; Using join buffer (incremental, BNL join)
 SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
 MIN(a)	b
 1	0

=== modified file 'mysql-test/t/func_group_innodb.test'
--- a/mysql-test/t/func_group_innodb.test	2013-09-14 01:09:36 +0000
+++ b/mysql-test/t/func_group_innodb.test	2014-04-22 21:39:57 +0000
@@ -180,7 +180,12 @@
 
 CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT,
                 KEY(b, c, a(765))) ENGINE=INNODB;
-INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
+INSERT INTO t1(a, b, c) VALUES 
+('', 'a', 0), ('', 'a', null), ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
+                               
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
 
 SELECT MIN(c) FROM t1 GROUP BY b;
 EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2014-02-03 14:22:39 +0000
+++ b/mysql-test/t/group_by.test	2014-04-22 21:39:57 +0000
@@ -1334,7 +1334,7 @@
    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
 
 let $query0=SELECT col1 AS field1, col1 AS field2
-            FROM t1 GROUP BY field1, field2+0;
+            FROM t1 GROUP BY field1, field2;
 
 # Needs to be range to exercise bug
 --eval EXPLAIN $query0;
@@ -1496,8 +1496,7 @@
   b varchar(1),
   KEY (b,a)
 );
-
-INSERT INTO t1 VALUES (1,NULL),(0,'a');
+INSERT INTO t1 VALUES (1,NULL),(0,'a'),(1,NULL),(0,'a');
 
 let $query=
   SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b;

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2013-11-20 11:05:39 +0000
+++ b/mysql-test/t/group_min_max.test	2014-04-22 21:39:57 +0000
@@ -15,7 +15,7 @@
 --enable_warnings
 
 create table t1 (
-  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
+  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' '
 );
 
 insert into t1 (a1, a2, b, c, d) values
@@ -65,7 +65,7 @@
 --enable_warnings
 
 create table t2 (
-  a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' '
+  a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' '
 );
 insert into t2 select * from t1;
 # add few rows with NULL's in the MIN/MAX column
@@ -1058,6 +1058,7 @@
 CREATE TABLE t (a INT, b INT, INDEX (a,b));
 INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1);
 INSERT INTO t SELECT * FROM t;
+INSERT INTO t SELECT * FROM t;
 
 --echo # test MIN
 --echo #should use range with index for group by
@@ -1257,6 +1258,7 @@
 
 CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
 INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
 CREATE TABLE t2 (c int) ;
 INSERT INTO t2 VALUES (0),(1);
 

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2014-04-18 10:07:54 +0000
+++ b/sql/opt_range.cc	2014-04-22 21:43:13 +0000
@@ -13429,7 +13429,7 @@
 
   DESCRIPTION
     This method computes the access cost of a TRP_GROUP_MIN_MAX instance and
-    the number of rows returned. It updates this->read_cost and this->records.
+    the number of rows returned.
 
   NOTES
     The cost computation distinguishes several cases:
@@ -13485,7 +13485,6 @@
   double p_overlap; /* Probability that a sub-group overlaps two blocks. */
   double quick_prefix_selectivity;
   double io_cost;
-  double cpu_cost= 0; /* TODO: CPU cost of index_read calls? */
   DBUG_ENTER("cost_group_min_max");
 
   table_records= table->stat_records();
@@ -13533,11 +13532,25 @@
              (double) num_blocks;
 
   /*
-    TODO: If there is no WHERE clause and no other expressions, there should be
-    no CPU cost. We leave it here to make this cost comparable to that of index
-    scan as computed in SQL_SELECT::test_quick_select().
+    CPU cost must be comparable to that of an index scan as computed
+    in SQL_SELECT::test_quick_select(). When the groups are small,
+    e.g. for a unique index, using index scan will be cheaper since it
+    reads the next record without having to re-position to it on every
+    group. To make the CPU cost reflect this, we estimate the CPU cost
+    as the sum of:
+    1. Cost for evaluating the condition (similarly as for index scan).
+    2. Cost for navigating the index structure (assuming a b-tree).
+       Note: We only add the cost for one comparision per block. For a
+             b-tree the number of comparisons will be larger.
+       TODO: This cost should be provided by the storage engine.
   */
-  cpu_cost= (double) num_groups / TIME_FOR_COMPARE;
+  const double tree_traversal_cost= 
+    ceil(log(static_cast<double>(table_records))/
+         log(static_cast<double>(keys_per_block))) * 
+    1/double(2*TIME_FOR_COMPARE); 
+
+  const double cpu_cost= num_groups *
+                         (tree_traversal_cost + 1/double(TIME_FOR_COMPARE));
 
   *read_cost= io_cost + cpu_cost;
   *records= num_groups;



More information about the commits mailing list