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

Make SUBSTR(col, 1, n) = const_str sargable

Details

    Description

      Conditions looking like

      SUBSTR(str_col, 1, n) = const_str

      where the prefix of str_col is compared against a constant value can be rewritten as range conditions as

      const_str_low <= str_col <= const_str_high

      which will improve the optimizer capabilities of building better execution plan (for example, using indexes on column str_col and applying range optimizations).
      The similar transformation is already implemented for conditions like

      str_col LIKE 'foo%'

      aiming for the same goal.

      Similar rewrite is implemented for DATE() and YEAR() function at MDEV-8320, and that approach may be used as a reference.

      Attachments

        Issue Links

          includes

          Task - A task that needs to be done. MDEV-4414 INDEX - SUBSTRING, LEFT and others string functions that could be optimized with index

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35564 cannot convert backslash to swe7

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Yuchen Pei made transition -
            Open In Progress
            		48d 15h 14m 1
            Yuchen Pei made transition -
            In Progress In Review
            		42s 1
            Sergei Petrunia made transition -
            In Review Stalled
            		8d 12h 22m 2
            Yuchen Pei made transition -
            Stalled In Review
            		13d 9h 52m 2
            Yuchen Pei made transition -
            In Review In Testing
            		5d 19h 47m 1
            Lena Startseva made transition -
            In Testing Stalled
            		23d 15h 33m 1
            Yuchen Pei made transition -
            Stalled Closed
            		13d 8h 23m 1

            People

              ycp Yuchen Pei
              oleg.smirnov Oleg Smirnov
              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.