• Michael Widenius's avatar
    Improve table pruning in optimizer with up to date key_dependent map · 432a4ebe
    Michael Widenius authored
    Part of:
    MDEV-28073 Slow query performance in MariaDB when using many tables
    
    s->key_dependent has a list of tables that are compared with key fields
    in the current table.  However it does not take into account if a key
    field could be resolved by another table.
    This is because MariaDB expands 'join_tab->keyuse' to include all generated
    comparisons.
    For example:
    SELECT * from t1,t2,t3 where t1.key=t2.key and t2.key=t3.key
    In this case keyuse for t1 includes t2.key and t3.key and key_dependent
    contains 't2.map | t3.map'
    If we in best_extension_by_limited_search() consider t2,t1 then t1's
    key is fully defined, but we cannot do any prune of plans as
    s->key_dependent indicates that t3 is still needed.
    
    Fixed by calculating in best_access_patch the current key_dependent map
    of tables that is needed to satisfy all keys. This allows us to prune
    more bad plans earlier as soon as all keys can be used.
    
    We also set key_dependent to 0 if we found an EQ_REF key, as this an
    optimal key for the table and there is no reason to check more keys.
    432a4ebe
selectivity_innodb.result 104 KB