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

        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.