Details
Description
The reason behind this change is that MariaDB 10.x does not take into
account that for engines like InnoDB, that scanning a primary key can be up
to 7x faster than scanning a secondary key + read the row data trough the
primary key.
optimizer-adjust_secondary_key_costs is added to provide 2 small
adjustments to the 10.x optimizer cost model. This can be used in the
case where the optimizer wrongly uses a secondary key instead of a
clustered primary key.
The different values for the new variable:
optimizer_adjust_secondary_key_costs=0 (default)
- No changes to current model
optimizer_adjust_secondary_key_costs=1
- Ensure that the cost of of secondary indexes has a cost of at
least 5x times the cost of a primary key.
This disables part of the worst_seek optimization described below.
optimizer_adjust_secondary_key_costs=2
- Disable "worst_seek optimization and adjust filter cost slightly
(add cost of 1 if filter is used).
Attachments
Issue Links
- relates to
-
MDEV-33306 Optimizer choosing incorrect index in 10.6, 10.5 but not in 10.4
- Closed
-
MDEV-33471 Optimizer: add sys var optimizer_adjust_secondary_key_costs
- Closed
-
MDEV-29226 Optimizer does not use the best index for range access
- Closed