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

Ref-or-ref optimization

    XMLWordPrintable

Details

    • New Feature
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 13.3
    • Optimizer
    • None

    Description

      The task is to add optimization for the following type of not that uncommon
      expressions:

      ... WHERE key1=value1 or key1=value2 or ...

      Currently MariaDB cannot use key1 for expression like the above and will
      instead use another key or a full scan.

      Here value1 or value2 can be constants, other fields or any expression
      that the optimizer can use for 'key1=value'.

      We could also use the same optimization of key1 IN (value1, value2,...)
      Note that key1 above could also be a key part (key1_part2)

      Some implementation suggestions:

      • Add a JT_EQ_REF_MANY type and extend expression that test for
        JT_EQ_REF to also handle JT_EQ_REF_MANY if appropriate (For example
        in add_not_null_conds)
      • Change in KEY_USE Item *val to be Item **val (null terminated array)
      • update_ref_and_keys() should be updated to fill in all values for a key part
      • In best_access_patch add support for multiple *val. The cost should be
        cost(key=value1) + cost(key=value2).
      • Note that capping of blocks read from disk, should not change
        (this should probably work automatically if we use
        Cost_estimate::add() to add the costs).
      • Add a new read method to pick_table_access_method(). This should be
        similar to what we have for JT_REF_OR_NULL, with the following
        exceptions:
      • While looping over all key different key values, we should
        remember the previous ones and ignore the new one if it matches
        any of the previous ones.
      • Update 'optimize_keyuse() for multiple values
      • We should check if we can move JT_REF_OR_NULL to be handled by the new code.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:

              Git Integration

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