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

Spider: query with long IN clause has performance issues

Details

    Description

      A simple SELECT query from SPIDER table is slow (~90sec) if WHERE has an IN-list with thousands (5558) of elements. InnoDB has no problems with this - the same query returns result in less than 1 sec.

      If the number of elements is reduced, then SPIDER works well too - for example with 8 it takes .003 sec.

      SELECT cause_id, location_id, year_id, sex_id, age_group_id, val AS current
      		 
      FROM output_epi_single_year_v19063_prevalence
      		 
      WHERE metric_id = 1
      		 
      AND measure_id = 5
      		 
      AND year_id = 2022
      		 
      AND sex_id IN (2)
      		 
      AND location_id IN (1,4,5,6,7,8,9,10)
      		 
      AND age_group_id IN (3)
      		 
      AND cause_id IN (294);
      		 
       
      		 
      8 rows in set (0.003 sec)

      DDL and EXPLAIN FORMAT=JSON added.

      Attachments

        1. DDL.txt
          3 kB
        2. explain.txt
          26 kB
        3. spider_global_variables.txt
          3 kB

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery.

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32273 Broken queries are passed to the group by handler for execution

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Activity

            People

              ycp Yuchen Pei
              YK Yakov Kushnirsky
              Votes:
              1 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.