Fix calculation of selectivity

calculate_cond_selectivity_for_table() is largely rewritten:
- Process keys in the order of rows found, smaller ranges first. If two
  ranges has equal number of rows, use the one with more key parts.
  This helps us to mark more used fields to not be used for further
  selectivity calculations. See cmp_quick_ranges().
- Ignore keys with fields that where used by previous keys
- Don't use rec_per_key[] to calculate selectivity for smaller
  secondary key parts.  This does not work as rec_per_key[] value
  is calculated in the context of the previous key parts, not for the
  key part itself. The one exception is if the previous key parts
  are all constants.

Other things:
- Ensure that select->cond_selectivity is always between 0 and 1.
- Ensure that select->opt_range_condition_rows is never updated to
  a higher value. It is initially set to the number of rows in table.
- We now store in table->opt_range_condition_rows the lowest number of
  rows that any row-read-method has found so far. Before it was only done
  for QUICK_SELECT_I::QS_TYPE_ROR_UNION and
  QUICK_SELECT_I::QS_TYPE_INDEX_MERGE.
  Now it is done for a lot more methods. See
  calculate_cond_selectivity_for_table() for details.
- Calculate and use selectivity for the first key part of a multiple key
  part if the first key part is a constant.
  WHERE key1_part1=5 and key2_part1=5.  IF key1 is used, then we can still
  use selectivity for key2

Changes in test results:
- 'filtered' is slightly changed, usually to something slightly smaller.
- A few cases where for group by queries the table order changed. This was
  because the number of resulting rows from a group by query with MIN/MAX
  is now set to be smaller.
- A few index was changed as we now prefer index with more key parts if
  the number of resulting rows is the same.

1 files changed, 7 insertions, 0 deletions

diff --git a/sql/opt_range.h b/sql/opt_range.h
index 7a067b49295..0973a9a944e 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -1109,6 +1109,13 @@ public:
   */
   uint used_key_parts;
 
+  /*
+    Set to 1 if we used group by optimization to calculate number of rows
+    in the result, stored in table->opt_range_condition_rows.
+    This is only used for asserts.
+  */
+  bool group_by_optimization_used;
+
   QUICK_SELECT_I();
   virtual ~QUICK_SELECT_I(){};