• Monty's avatar
    Fix calculation of selectivity · dc2f0d13
    Monty authored
    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.
    dc2f0d13
group_min_max.result 142 KB