Using optimizer_adjust_secondary_key_costs in 10.6-10.11
Using optimizer_adjust_secondary_key_costs
In 10.6 we added a new server variable, optimizer_adjust_secondary_key_costs, to give the user the ability to affect how the cost for for secondary keys using ref are calculated in the few cases when MariaDB 10.6 up to 10.11 makes a sub optimal choose when optimizing ref access, either for key lookups or GROUP BY.
ref, as used by EXPLAIN, means that the optimizer is using key-lookup on one value to find the matching rows from a table.
The values for optimizer_adjust_secondary_key_costs is one of more of the following:
| Value | Default behavior | Change when variable is used |
|---|---|---|
| disable_max_seek | ref cost on secondary keys is limited to max_seek = min('number of expected rows'/ 10, scan_time*3) | Disable 'max_seek' optimization and do a slight adjustment of filter cost |
| adjust_secondary_key_cost | The secondary key costs for ref is updated to be at least 5x of clustered primary key costs if a clustered primary key exists | |
| disable_forced_index_in_group_by | Use a rule based choice when deciding to use an index to resolve GROUP BY | The choice is now cost based |
One can set all options with:
set @@optimizer_adjust_secondary_key_costs='all';
Explanations of some of the old behavior in MariaDB 10.#.
The reason for the max_seek optimization was originally to ensure that MariaDB would use a key instead of a table scan. This works good for a lot of queries, but can cause problems when a table scan is a better choice, like when one would have to scan more than 1/4 of the rows in the table (in which case a table scan is better).
Other things
optimizer_adjust_secondary_key_costs will be obsolete starting from 11.0 as the new optimizer in 11.0 does not have max_seek optimization and is already using cost based choices for index usage with GROUP BY.
