• Monty's avatar
    In best_access_path() change record_count to 1.0 if its less than 1.0. · 8b7c0d69
    Monty authored
    In essence this means that we expect the user query to have at least
    one matching row in the end.
    This change will not affect the estimated rows for the plan, but will
    ensure that the cost for adding a table is not neglected because of
    record count being too low.
    
    The reasons for this is that if we have table combination that
    together has a very high selectivity then join record_count could
    become very low (close to 0)
    
    This would cause costs for all future tables to be so small that they
    are irrelevant for the rest of the plan.
    This has been shown to be the case in some performance benchmarks and
    in a few mtr tests.
    
    There is also still a problem in selectivity calculations as joining two
    tables in different order causes a different estimation of total rows.
    This can be seen in selectivity_innodb.test, test 'Q20' where joining
    nation,supplier is expecting 1.111 rows_out while joining supplier,nation
    is expecting 0.04 rows_out.
    
    The reason for 0.04 is that the optimizer estimates 'supplier' to have
    10 matching rows, and joining with nation (eq_ref) has 1 row. However
    selectivity of n_name = 'UNITED STATES' makes the optimizer things
    that there will be only 0.04 matching rows.
    
    This patch avoids this "too low row count" to affect cost
    caclulations.
    8b7c0d69
selectivity_innodb.result 107 KB