-
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