From: Sergey Petrunia
Date: December 23 2005 10:28pm
Subject: bk commit into 5.0 tree (sergefp:1.2000)
List-Archive: http://lists.mysql.com/commits/396
Message-Id: <20051223222806.AF7C82D7B6A@newbox.mylan>

Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2000 05/12/24 01:28:00 sergefp@stripped +1 -0
  Optimizer fix: Try to make more intelligent reuse of range optimizer #records estimate as 
  'ref' access estimate, added comment about an odd property I've discovered.

  sql/sql_select.cc
    1.378 05/12/24 01:27:50 sergefp@stripped +27 -1
    Optimizer fix: Try to make more intelligent reuse of range optimizer #records estimate as 
    'ref' access estimate, added comment about an odd property I've discovered.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-5.0-csc7696

--- 1.377/sql/sql_select.cc	2005-12-19 14:35:57 +03:00
+++ 1.378/sql/sql_select.cc	2005-12-24 01:27:50 +03:00
@@ -3372,6 +3372,19 @@
             /*
               Check if quick_range could determinate how many rows we
               will match
+              TODO:
+              Currently we reuse the #records estimate of range access as 
+              #records estimate for 'ref' access even when these estimates
+              scan different ranges, eg for condition
+              
+                (tbl.key < 10) AND (tbl.key = othertbl.field)
+
+              we'll use E(#records_such_that(tbl.key < 10)) as 
+              E(#records_such_that(tbl.key =<some value>)).
+
+              Ask if this is really intentional, and if not, reuse range
+              access estimate iff it was created for scanning one singlepoint
+              interval.
             */
             if (table->quick_keys.is_set(key) &&
                 table->quick_key_parts[key] == max_key_part)
@@ -3380,7 +3393,20 @@
             {
               /* Check if we have statistic about the distribution */
               if ((records = keyinfo->rec_per_key[max_key_part-1]))
-                tmp = records;
+              {
+                /* 
+                  Fix for the case where the index statistics is too
+                  optimistic: If quick select uses longer key, and has
+                  higher #records estimate (so we have higher [and more
+                  precise] estimate for scanning the same or smaller number
+                  of records), use that estimate.
+                */
+                if (table->quick_keys.is_set(key) &&
+                    table->quick_key_parts[key] > max_key_part &&
+                    records < (double)table->quick_rows[key])
+                  records= (double)table->quick_rows[key];
+                tmp= records;
+              }
               else
               {
                 /*