Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33118

Add optimizer_adjust_secondary_key_costs variable

    XMLWordPrintable

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

          Activity

            People

              monty Michael Widenius
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.