• 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

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.

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


Comments loading...
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.