-
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