• Sergey Petrunya's avatar
    MDEV-6081: ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686) · 244d4b53
    Sergey Petrunya authored
    Add a testcase and backport this fix:
    
    Bug#14338686: MYSQL IS GENERATING DIFFERENT AND SLOWER
                  (IN NEWER VERSIONS) EXECUTION PLAN
    PROBLEM:
    While checking for an index to sort for the order by clause
    in this query
    "SELECT datestamp FROM contractStatusHistory WHERE
    contract_id = contracts.id ORDER BY datestamp asc limit 1;"
    
    we do not calculate the number of rows to be examined correctly.
    As a result we choose index 'idx_contractStatusHistory_datestamp'
    defined on the 'datestamp' field, rather than choosing index
    'contract_id'. And hence the lower performance.
    
    ANALYSIS:
    While checking if an index is present to give the records in
    sorted order(datestamp), we consider the selectivity of the
    'ref_key'(contract_id here) using 'table->quick_condition_rows'.
    'ref_key' here can be an index from 'REF_ACCESS' or from 'RANGE'.
    
    As this is a 'REF_ACCESS', 'table->quick_condition_rows' is not
    set to the actual value which is 2. Instead is set to the number
    of tuples present in the table indicating that every row that
    is selected would be satisfying the condition present in the query.
    
    Hence, the selectivity becomes 1 even when we choose the index
    on the order by column instead of the join_condition.
    
    But, in reality as only 2 rows satisy the condition, we need to
    examine half of the entire data set to get one tuple when we
    choose index on the order by column.
    Had we chosen the 'REF_ACCESS' we would have examined only 2 tuples.
    Hence the delay in executing the query specified.
      
    FIX:
    While calculating the selectivity of the ref_key:
    For REF_ACCESS consider quick_rows[ref_key] if range 
    optimizer has an estimate for this key. Else consider 
    'rec_per_key' statistic.
    For RANGE ACCESS consider 'table->quick_condition_rows'.
    244d4b53
subselect_innodb.result 17.8 KB