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

Queries with IN clauses that are pushing the optimizer to its limit

    XMLWordPrintable

    Details

      Description

      Consider a table

      create table t1234 (
        campaignid varchar(32) not null,
        adgroupid varchar(32) not null,
        col1 int not null,
        PRIMARY KEY (campaignid,adgroupid),
        UNIQUE KEY adgroupid (adgroupid)
      );
      

      and a query in form

      explain
      select * from t1234
      where 
         campaignid='Finland' AND adgroupid IN ('Lahti','Imatra')
         OR
         campaignid='Russia' AND adgroupid IN ('Petersburg', 'Moscow')
         OR 
         campaignid='Sweden' AND adgroupid IN ('Stockholm', 'Uppsala')
         OR 
         campaignid='Germany' AND adgroupid IN ('Berlin', 'Frankfurt')
         OR 
         campaignid='Belgium' AND adgroupid IN ('Brussels', 'Gent')
         OR 
         campaignid='Portugal' AND adgroupid IN ('Lisbon', 'Porto')
      ...
      

      The query

      • takes a lot of time to run (more details to follow about this).
      • May switch to using full table scan when it is still better to use the range access.

      The reason seems to be that MariaDB's optimizer is trying to construct index_merge access plans which are not particularly meaningful ( adding ignore index (adgroupid) resolves the issue).

        Attachments

        1. mdev11574-debug-printout.diff
          11 kB
          Sergei Petrunia
        2. mdev11574-sel_tree.txt
          16 kB
          Sergei Petrunia
        3. mdev11574-sel_tree-read.txt
          3 kB
          Sergei Petrunia

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              psergei Sergei Petrunia
              Votes:
              3 Vote for this issue
              Watchers:
              6 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.