[Commits] Rev 3480: MDEV-446: Reuse quick_condition_rows in fanout computations in file:///home/tsk/mprog/src/10.0-md446/

timour at askmonty.org timour at askmonty.org
Thu Nov 15 16:47:23 EET 2012


At file:///home/tsk/mprog/src/10.0-md446/

------------------------------------------------------------
revno: 3480
revision-id: timour at askmonty.org-20121115144706-wiuxb5svyrz23k0l
parent: timour at askmonty.org-20121115105450-zkiva73utg8pg49j
fixes bug: https://mariadb.atlassian.net/browse/MDEV-446
committer: timour at askmonty.org
branch nick: 10.0-md446
timestamp: Thu 2012-11-15 16:47:06 +0200
message:
  MDEV-446: Reuse quick_condition_rows in fanout computations
  
  Intermediate commit.
  
  The patch adds JOIN_TAB::cond_selectivity(). There still are 5 test cases
  where EXPLAINs need further investigation why they got worse. The test files are:
  main.subselect_mat_cost main.join_cache main.select_pkeycache main.select main.select_jcl6
  Where the latter have the same difference.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-11-03 11:28:51 +0000
+++ b/mysql-test/r/subselect.result	2012-11-15 14:47:06 +0000
@@ -558,7 +558,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1371,7 +1371,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ref     salary  salary  5       const   0       0.00    Using where
+1       PRIMARY t1      ref     salary  salary  5       const   0       100.00  Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -1878,13 +1878,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-11-03 11:28:51 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-11-15 14:47:06 +0000
@@ -565,7 +565,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1378,7 +1378,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ref     salary  salary  5       const   0       0.00    Using where
+1       PRIMARY t1      ref     salary  salary  5       const   0       100.00  Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -1885,13 +1885,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`id`>(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`))))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-11-03 11:28:51 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-11-15 14:47:06 +0000
@@ -561,7 +561,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ref     salary  salary  5       const   0       0.00    Using where
+1       PRIMARY t1      ref     salary  salary  5       const   0       100.00  Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -1881,13 +1881,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-11-03 11:28:51 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-11-15 14:47:06 +0000
@@ -564,7 +564,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1377,7 +1377,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ref     salary  salary  5       const   0       0.00    Using where
+1       PRIMARY t1      ref     salary  salary  5       const   0       100.00  Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -1884,13 +1884,13 @@ id	text
 explain extended select * from t1 where id not in (select id from t1 where id < 8);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       100.00  Using index; Using where
+2       DEPENDENT SUBQUERY      t1      unique_subquery PRIMARY PRIMARY 4       func    1       58.33   Using index; Using where
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where ((`test`.`t1`.`id` < 8) and (<cache>(`test`.`t1`.`id`) = `test`.`t1`.`id`)))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-11-03 11:28:51 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-11-15 14:47:06 +0000
@@ -561,7 +561,7 @@ id	select_type	table	type	possible_keys
 Note    1003    select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       100.00  Using where; Using index
+1       PRIMARY t1      const   PRIMARY,numreponse      PRIMARY 7       const,const     1       66.67   Using where; Using index
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_in
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       PRIMARY t1      ref     salary  salary  5       const   0       0.00    Using where
+1       PRIMARY t1      ref     salary  salary  5       const   0       100.00  Using where
 2       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away
 Warnings:
 Note    1003    select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -1887,7 +1887,7 @@ Note	1003	select `test`.`t1`.`id` AS `id
 explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1       PRIMARY tt      ALL     NULL    NULL    NULL    NULL    12      100.00  Using where
-2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       100.00  Using where; Using index
+2       DEPENDENT SUBQUERY      t1      eq_ref  PRIMARY PRIMARY 4       test.tt.id      1       58.33   Using where; Using index
 Warnings:
 Note    1276    Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
 Note    1003    select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(<expr_cache><`test`.`tt`.`id`>(exists(select `test`.`t1`.`id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))))

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-11-15 10:54:50 +0000
+++ b/sql/sql_select.cc	2012-11-15 14:47:06 +0000
@@ -5301,7 +5301,8 @@ void set_position(JOIN *join,uint idx,JO
 /* Estimate of the number matching candidates in the joined table */
 
 inline
-ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint)
+ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint,
+                                     bool *use_range_stats)
 {
   ha_rows records= s->found_records;
   /*
@@ -5321,7 +5322,10 @@ ha_rows matching_candidates_in_table(JOI
       heuristics at once.
     */
   if (s->table->quick_condition_rows != s->found_records)
+  {
     records= s->table->quick_condition_rows;
+    *use_range_stats= false;
+  }
 
   return records;
 }
@@ -5385,9 +5389,9 @@ best_access_path(JOIN      *join,
   DBUG_ENTER("best_access_path");
   
   bitmap_clear_all(eq_join_set);
-
   loose_scan_opt.init(join, s, remaining_tables);
-  
+  pos->use_range_stats= test(s->quick);
+
   if (s->keyuse)
   {                                            /* Use key if possible */
     KEYUSE *keyuse;
@@ -5395,6 +5399,7 @@ best_access_path(JOIN      *join,
     TABLE *table= s->table;
     double best_records= DBL_MAX;
     uint max_key_part=0;
+    bool cur_use_range_stats;
 
     /* Test how we can use keys */
     rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE;  // Assumed records/key
@@ -5411,6 +5416,7 @@ best_access_path(JOIN      *join,
       key_part_map const_part= 0;
       /* The or-null keypart in ref-or-null access: */
       key_part_map ref_or_null_part= 0;
+      cur_use_range_stats= true;
       if (is_hash_join_key_no(key))
       {
         /* 
@@ -5540,7 +5546,10 @@ best_access_path(JOIN      *join,
                 empty interval we wouldn't have got here).
               */
               if (table->quick_keys.is_set(key))
+              {
                 records= (double) table->quick_rows[key];
+                cur_use_range_stats= false;
+              }
               else
               {
                 /* quick_range couldn't use key! */
@@ -5577,6 +5586,7 @@ best_access_path(JOIN      *join,
                   records > (double) table->quick_rows[key])
               {
                 records= (double) table->quick_rows[key];
+                cur_use_range_stats= false;
               }
             }
             /* Limit the number of matched rows */
@@ -5646,6 +5656,7 @@ best_access_path(JOIN      *join,
                 table->quick_n_ranges[key] == 1+test(ref_or_null_part)) //(C3)
             {
               tmp= records= (double) table->quick_rows[key];
+              cur_use_range_stats= false;
             }
             else
             {
@@ -5671,7 +5682,24 @@ best_access_path(JOIN      *join,
                 if (!found_ref && table->quick_keys.is_set(key) &&    // (1)
                     table->quick_key_parts[key] > max_key_part &&     // (2)
                     records < (double)table->quick_rows[key])         // (3)
+                {
                   records= (double)table->quick_rows[key];
+                  cur_use_range_stats= false;
+                }
+
+                /*
+                  If not all keys are constant (i.e. we have a join), and the
+                  tightest range statistics is computed for a range that is a
+                  prefix of the key used for the join, then the range estimate
+                  is not independent of the key statistics estimate. In this
+                  case the where part selectivity deduced from the range estimate
+                  cannot be used to improve the join selectivity estimate.
+                */
+                if (found_ref &&
+                    table->quick_keys.is_set(key) &&
+                    table->quick_key_parts[key] <= max_key_part &&
+                    table->quick_rows[key] == table->quick_condition_rows)
+                  cur_use_range_stats= false;
 
                 tmp= records;
               }
@@ -5741,6 +5769,7 @@ best_access_path(JOIN      *join,
                   records > (double) table->quick_rows[key])
               {
                 tmp= records= (double) table->quick_rows[key];
+                cur_use_range_stats= false;
               }
             }
 
@@ -5768,6 +5798,7 @@ best_access_path(JOIN      *join,
         best_key= start_key;
         best_max_key_part= max_key_part;
         best_ref_depends_map= found_ref;
+        pos->use_range_stats= cur_use_range_stats;
       }
     } /* for each key */
     records= best_records;
@@ -5792,7 +5823,8 @@ best_access_path(JOIN      *join,
   {
     double join_sel= 0.1;
     /* Estimate the cost of  the hash join access to the table */
-    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
+    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint,
+                                                      &pos->use_range_stats);
 
     tmp= s->quick ? s->quick->read_time : s->scan_time();
     tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
@@ -5851,7 +5883,8 @@ best_access_path(JOIN      *join,
       !(s->table->force_index && best_key && !s->quick) &&               // (4)
       !(best_key && s->table->pos_in_table_list->jtbm_subselect))        // (5)
   {                                             // Check full join
-    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
+    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint,
+                                                      &pos->use_range_stats);
 
     /*
       Range optimizer never proposes a RANGE if it isn't better
@@ -5933,8 +5966,18 @@ best_access_path(JOIN      *join,
     }
   }
 
+  if (s->quick && !best_key)
+  {
+    /*
+      Range access is chosen if there is a quick select, and there is no
+      [eq]ref. In this case range selectivity is already accounted for.
+    */
+    pos->use_range_stats= false;
+  }
+
   /* Update the cost information for the current partial plan */
-  pos->records_read= records;
+  s->use_range_stats= pos->use_range_stats;
+  pos->records_read= records * s->cond_selectivity(idx);
   pos->read_time=    best;
   pos->key=          best_key;
   pos->table=        s;
@@ -6949,6 +6992,7 @@ best_extension_by_limited_search(JOIN
   double best_record_count= DBL_MAX;
   double best_read_time=    DBL_MAX;
   bool disable_jbuf= join->thd->variables.join_cache_level == 0;
+  double partial_join_cardinality;
 
   DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time,
                                 "part_plan"););
@@ -7036,10 +7080,12 @@ best_extension_by_limited_search(JOIN
       if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) & allowed_tables )
       { /* Recursively expand the current partial plan */
         swap_variables(JOIN_TAB*, join->best_ref[idx], *pos);
+        partial_join_cardinality= current_record_count *
+                                  s->cond_selectivity(idx);
         if (best_extension_by_limited_search(join,
                                              remaining_tables & ~real_table_bit,
                                              idx + 1,
-                                             current_record_count,
+                                             partial_join_cardinality,
                                              current_read_time,
                                              search_depth - 1,
                                              prune_level))
@@ -7768,6 +7814,7 @@ get_best_combination(JOIN *join)
       to access join->best_positions[]. 
     */
     j->records_read= join->best_positions[tablenr].records_read;
+    j->use_range_stats= join->best_positions[tablenr].use_range_stats;
     join->map2table[j->table->tablenr]= j;
 
     /* If we've reached the end of sjm nest, switch back to main sequence */
@@ -10454,12 +10501,28 @@ double JOIN_TAB::scan_time()
 
 
 /**
+  The added selectivity of the part of the WHERE clause pushed to this JOIN_TAB
+  after applying its table access method. If an access method covers the whole
+  WHERE part, then this selectivity is 1 because the WHERE part doesn't filter
+  any additional rows in addition to the access method.
+*/
+
+double JOIN_TAB::cond_selectivity(int idx __attribute__((unused)))
+{
+  if (!use_range_stats || !table || !table->file->stats.records)
+    return 1;
+  return ((double)table->quick_condition_rows /
+          (double)table->file->stats.records);
+}
+
+
+/**
   Estimate the number of rows that a an access method will read from a table.
 
   @todo: why not use JOIN_TAB::found_records
 */
 
-ha_rows JOIN_TAB::get_examined_rows()
+double JOIN_TAB::get_examined_rows()
 {
   double examined_rows;
 
@@ -10491,7 +10554,14 @@ ha_rows JOIN_TAB::get_examined_rows()
     }
   }
   else
-    examined_rows= records_read;
+  {
+    /*
+      The number of records examined by the access method, before applying
+      the WHERE condition pushed to this plan operator.
+    */
+    examined_rows= records_read /
+      cond_selectivity(/* todo - put the real position */0);
+  }
 
   return examined_rows;
 }

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-11-15 10:54:50 +0000
+++ b/sql/sql_select.h	2012-11-15 14:47:06 +0000
@@ -282,7 +282,9 @@ typedef struct st_join_table {
   
   /* Copy of POSITION::records_read, set by get_best_combination() */
   double       records_read;
-  
+  /* Copy of POSITION::use_range_stats */
+  bool use_range_stats;
+
   /* Startup cost for execution */
   double        startup_cost;
     
@@ -510,7 +512,8 @@ typedef struct st_join_table {
     return (is_hash_join_key_no(key) ? hj_key : table->key_info+key);
   }
   double scan_time();
-  ha_rows get_examined_rows();
+  double cond_selectivity(int idx __attribute__((unused)));
+  double get_examined_rows();
   bool preread_init();
 
   bool is_sjm_nest() { return test(bush_children); }
@@ -752,6 +755,13 @@ typedef struct st_position :public Sql_a
     previous tables.
   */
   double records_read;
+  /*
+    TRUE if range statistics can be used to estimate the selectivity of the
+    WHERE clause in addition to the records per key index statistics. This is
+    the case when range statistics was not already taken into account in fanout
+    estimates of the current access method.
+  */
+  bool use_range_stats;
 
   /* 
     Cost accessing the table in course of the entire complete join execution,



More information about the commits mailing list