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

Query with long IN(...) list in WHERE about 40 times slower

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.11
    • 10.4
    • None
    • Ubuntu 18.04
      MariaDB Repository
      RAM 32GB
      8 CPU cores

    Description

      Query that used to take about 5 seconds in MariaDB 10.2.19 now needs 200 seconds.
      List in IN is about 1500 values.

      MariaDB 10.2.19:

      # Time: 181122  9:56:07
      # User@Host: 
      # Thread_id: 6298341  Schema: leitsystem  QC_hit: No
      # Query_time: 6.211773  Lock_time: 0.000545  Rows_sent: 25  Rows_examined: 825043
      # Rows_affected: 0
      # Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
      # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
      #
      # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
      # explain: 1    SIMPLE  zr_te_value     range   PRIMARY,ts      PRIMARY 9       NULL    420882  412509.00       100.00  100.00  Using where; Using temporary; Using filesort
      #
      SET timestamp=1542876967;
      SELECT CONCAT("9906643000004NEXT", LPAD(next_id_int, 16, "0")) AS next_id, ts - MOD(ts, 300), ROUND(AVG(value), 3), AVG(quality) FROM leitsystem.zr_te_value WHERE next_id_in
      t IN (6447,5272,...,3104) 
      AND ts >= 1542668700 AND ts < 1542755400 AND zr_art = 12 GROUP BY next_id, ts DIV 300 ORDER BY ts ASC LIMIT 0, 25;;
      

      MariaDB 10.3.11:

      # Time: 181122  9:20:42
      # User@Host: 
      # Thread_id: 82  Schema: leitsystem  QC_hit: No
      # Query_time: 214.707173  Lock_time: 0.000781  Rows_sent: 25  Rows_examined: 211389323
      # Rows_affected: 0  Bytes_sent: 1773
      # Tmp_tables: 3  Tmp_disk_tables: 0  Tmp_table_sizes: 92843912
      # Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: No
      # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
      #
      # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
      # explain: 1    PRIMARY <subquery2>     ALL     distinct_key    NULL    NULL    NULL    1548    1548.00 100.00  100.00  Using temporary; Using filesort
      # explain: 1    PRIMARY zr_te_value     ref     PRIMARY,ts      PRIMARY 5       const,tvc_0._col_1      218     136287.92       100.00  0.20    Using where
      # explain: 2    MATERIALIZED    <derived3>      ALL     NULL    NULL    NULL    NULL    1548    1548.00 100.00  100.00
      # explain: 3    DERIVED NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
      #
      use leitsystem;
      SET timestamp=1542874842;
      SELECT CONCAT("9906643000004NEXT", LPAD(next_id_int, 16, "0")) AS next_id, ts - MOD(ts, 300), ROUND(AVG(value), 3), AVG(quality) FROM leitsystem.zr_te_value WHERE next_id_in
      t IN (6447,5272,...,3104) 
      AND ts >= 1542668700 AND ts < 1542755400 AND zr_art = 12 GROUP BY next_id, ts DIV 300 ORDER BY ts ASC LIMIT 0, 25;;
      

      Already discussed this in MDEV-12176

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              gunni Frank Sagurna
              Votes:
              6 Vote for this issue
              Watchers:
              17 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.