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 ref are calculated in the few cases when MariaDB 10.6 up to MariaDB 10.11 makes a sub-optimal choice 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. Unused from MariaDB 11.0.
  • 0: No changes (the default)
  • 1: Update secondary key costs for ranges to be at least 5x of clustered primary key costs.
  • 2: Remove 'max_seek optimization' for secondary keys and slight adjustment of filter cost.
• Commandline: --optimizer-adjust-secondary-key-costs=#
• Scope: Global, Session
• Dynamic: Yes
• Data Type: numeric
• Default Value: 0
• Range: 0 to 2
• Introduced: MariaDB 10.6.17, MariaDB 10.11.7, MariaDB 11.0.5, MariaDB 11.1.4, MariaDB 11.2.3

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.