optimizer_adjust_secondary_key_costs
The optimizer_adjust_secondary_key_costs setting, which tunes cost estimates for secondary keys.
optimizer_adjust_secondary_key_costs
Description: Gives the user the ability to affect how the costs for secondary keys using
refare calculated in the few cases when MariaDB 10.6 up to MariaDB 10.11 makes a sub-optimal choice when optimizingrefaccess, either for key lookups orGROUP 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. Unused from MariaDB 11.0. In MariaDB 10.6.18 the variable was changed from a number to a set of strings anddisable_forced_index_in_group_by(value 4) was added; the default was empty at that point. MariaDB 10.6.20 and MariaDB 10.11.10 addedfix_reuse_range_for_refandfix_card_multiplierand made both of them the default, so a server on MariaDB 10.6.18 or 10.6.19, or on MariaDB 10.11.8 or 10.11.9, reports an empty value unless it is configured otherwise.Scope: Global, Session
Dynamic: Yes
Data Type:
Numeric (in MariaDB 10.6.17 and MariaDB 10.11.7, and from MariaDB 11.0)
set(from MariaDB 10.6.18 and MariaDB 10.11.8 up to, but not including, MariaDB 11.0)
Default Value:
0(from MariaDB 11.0, when the variable returned to a numeric type)fix_reuse_range_for_ref,fix_card_multiplier(from MariaDB 10.6.20 and MariaDB 10.11.10, before MariaDB 11.0)Empty (from MariaDB 10.6.18 and MariaDB 10.11.8, when the data type became
set)0(in MariaDB 10.6.17 and MariaDB 10.11.7, when the data type was numeric)
Range:
0to2(in MariaDB 10.6.17 and MariaDB 10.11.7, and from MariaDB 11.0)Any combination of
adjust_secondary_key_cost,disable_max_seek, anddisable_forced_index_in_group_by, orall, or0to7(in MariaDB 10.6.18 and MariaDB 10.11.8)Any combination of the preceding values and
fix_innodb_cardinality, orall, or0to15(in MariaDB 10.6.19 and MariaDB 10.11.9)Any combination of the preceding values,
fix_reuse_range_for_ref, andfix_card_multiplier, orall, or0to63(from MariaDB 10.6.20 and MariaDB 10.11.10, up to, but not including, MariaDB 10.11.12 in the 10.11 series, and before MariaDB 11.0)Any combination of the preceding values and
fix_derived_table_read_cost, orall, or0to127(from MariaDB 10.11.12 up to, but not including, MariaDB 10.11.17)Any combination of the preceding values and
fix_order_by_index_choice, orall, or0to255(from MariaDB 10.11.17, before MariaDB 11.0)
Introduced: MariaDB 10.6.17, MariaDB 10.11.7
On a release that carries the current default, the variable reports both flags:
MariaDB starting with 11.0
optimizer_adjust_secondary_key_costs will be obsolete starting from MariaDB 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.
The value for optimizer_adjust_secondary_key_costs is one of more of the following:
adjust_secondary_key_cost
Limit ref costs by max_seeks
The secondary key costs for ref are updated to be at least five times the clustered primary key costs if a clustered primary key exists
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
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
fix_innodb_cardinality
By default InnoDB doubles the cardinality for indexes in an effort to force index usage over table scans. This can cause the optimizer to create sub-optimal plans for ranges or index entries that cover a big part of the table.
Using this option removes the doubling of cardinality in InnoDB. fix_innodb_cardinality is recommended to be used only as a server startup option, as it is enabled for a table at first usage. See MDEV-34664 for details.
fix_reuse_range_for_ref
Number of estimated rows for 'ref' did not always match costs from range optimizer
Use cost from range optimizer for 'ref' if all used key parts are constants. The old code did not always do this
fix_card_multiplier
Index selectivity can be bigger than 1.0 if index statistics is not up to date. Not on by default.
Ensure that the calculated index selectivity is never bigger than 1.0. Having index selectivity bigger than 1.0 causes MariaDB to believe that there is more rows in the table than in reality, which can cause wrong plans. This option is on by default.
fix_derived_table_read_cost
The cost of scanning a materialized derived table did not take into account that the internal temporary table can be converted from MEMORY to Aria when the data does not fit in memory, and assumed at least 1000 rows. Not on by default.
Estimate the scan cost with the engine the temporary table will actually use, and trust the optimizer's row estimate. See MDEV-35958 for details.
fix_order_by_index_choice
For queries with an ORDER BY ... LIMIT clause, the optimizer considers indexes that produce the desired ordering. Before MariaDB 11.0, it picks the index with fewer key parts, even if using that index means reading more rows or is more expensive. Not on by default.
Pick the index based on cost computations, which take into account how many rows must be read before the LIMIT is reached. See MDEV-38072 for details.
One can set all options with:
Explanations of the old behavior in MariaDB 10.x
The reason for the max_seek optimization was originally to ensure that MariaDB would use a key instead of a table scan. This works well for a lot of queries, but can cause problems when a table scan is a better choice, such as when one would have to scan more than 1/4 of the rows in the table (in which case a table scan is better).
See Also
The optimizer_switch system variable.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?