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

        Issue Links

          Activity

            People

              igor Igor Babaev
              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.