optimizer_adjust_secondary_key_costs
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. - Scope: Global, Session
- Dynamic: Yes
- Data Type:
set - Default Value:
"" - Range:
0to7or any combination ofadjust_secondary_key_cost,disable_max_seekordisable_forced_index_in_group_by. - Introduced: MariaDB 10.6.17, MariaDB 10.11.7, MariaDB 11.0.5, MariaDB 11.1.4, MariaDB 11.2.3
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:
| Value | Version added | Default behavior | Change when variable is used |
|---|---|---|---|
| adjust_secondary_key_cost | 10.6.17 | 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 | 10.6.17 | 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 | 10.6.18 | Use a rule-based choice when deciding to use an index to resolve GROUP BY | The choice is now cost based |
| fix_innodb_cardinality | 10.6.19 | 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. |
One can set all options with:
set @@optimizer_adjust_secondary_key_costs='all';
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.
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
