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

Make "unique_key NOT IN (...)" conditions non-sargable

    XMLWordPrintable

    Details

      Description

      This is a part of MDEV-21958.

      This MDEV covers "(Very basic) Make NOT-IN non-sargable" suggestion from here:
      https://jira.mariadb.org/browse/MDEV-21958?focusedCommentId=170957&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-170957

      The idea is to make conditions in form "unique_key NOT IN (const, .... )" non-sargable. The reasoning is: when the constant list is small, the condition is not selective. when the constant list is large (half the table or more), the overhead of processing the list by the optimizer is too high.

      The fix was pushed as these three patches:

      commit dcc7f93965f7fb534f29c5c682b2abf22e808fe2
      Author: Sergei Petrunia <psergey@askmonty.org>
      Date:   Fri Dec 11 22:45:54 2020 +0300
       
          MDEV-21958: Query having many NOT-IN clauses running forever, part 3
          
          Add the new file
      

      commit 502bc77f23715f84a049fe6c28a861e9af271016
      Author: Sergei Petrunia <psergey@askmonty.org>
      Date:   Fri Dec 11 22:44:13 2020 +0300
       
          MDEV-21958: Query having many NOT-IN clauses running forever, part 2
          
          Move the testcase into a separate file: embedded server
          doesn't have optimizer trace.
      

      commit 4addd31531f722438b8b702c9cd00c28b61efce3
      Author: Sergei Petrunia <psergey@askmonty.org>
      Date:   Fri Dec 11 18:54:21 2020 +0300
       
          MDEV-21958: Query having many NOT-IN clauses running forever
          
          Basic variant of the fix: do not consider conditions in form
          
            unique_key NOT IN (c1,c2...)
          
          to be sargable. If there are only a few constants, the condition
          is not selective. If there are a lot constants, the overhead of
          processing such a huge range list is not worth it.
      

      The fixes were pushed into 10.4, 10.5, and 10.6 trees.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration