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

Subquery in an UPDATE query uses full scan instead of range

    XMLWordPrintable

    Details

      Description

      Here is the testcase (with totally synthetic data) :

      CREATE TABLE t1 (
        key1 varchar(30) NOT NULL,
        col1 int(11) NOT NULL,
        filler char(100)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      insert into t1 
      select 
        seq, seq, seq
      from seq_1_to_100000;
      

      CREATE TABLE t10 (
        key1 varchar(30) NOT NULL,
        col1 int,
        filler char(100),
        PRIMARY KEY (key1)
      ) ENGINE=InnoDB CHARSET=utf8;
       
      insert into t10
      select 
       seq, seq, seq from seq_1_to_100000;
      

      CREATE TABLE t11 (
        key1 varchar(30) NOT NULL,
        filler char(100),
        PRIMARY KEY (key1)
      ) ENGINE=InnoDB CHARSET=utf8;
       
      insert into t11
      select 
       seq, seq from seq_1_to_100000;
      

      explain
      select * from t1 hist
      where
        hist.col1 NOT IN (SELECT tn.col1 
                          FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 
                          WHERE 
                             tn.key1 IN ('1','2','3','4','5','6','7','8','9','10')
                              ) \G
      

      explain update t1 hist
      set filler='aaa'
      WHERE 
        key1 IN ('1','2','3','4','5','6','7','8','9','10') AND 
        hist.col1 NOT IN (SELECT tn.col1
                          FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 
                          WHERE tn.key1 IN ('1','2','3','4','5','6','7','8','9','10')
                         ) \G
      

      EXPLAIN output for SELECT:

      *************************** 1. row ***************************
                 id: 1
        select_type: PRIMARY
              table: hist
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 99268
              Extra: Using where
      *************************** 2. row ***************************
                 id: 2
        select_type: MATERIALIZED
              table: tms
               type: range
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 92
                ref: NULL
               rows: 10
              Extra: Using where; Using index
      *************************** 3. row ***************************
                 id: 2
        select_type: MATERIALIZED
              table: tn
               type: eq_ref
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 92
                ref: j5.tms.key1
               rows: 1
              Extra: 
      

      Note that table tms uses range access.

      EXPLAIN output for the UPDATE:

      *************************** 1. row ***************************
                 id: 1
        select_type: PRIMARY
              table: hist
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 99268
              Extra: Using where
      *************************** 2. row ***************************
                 id: 2
        select_type: DEPENDENT SUBQUERY
              table: tms
               type: index
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 92
                ref: NULL
               rows: 85462
              Extra: Using where; Using index
      *************************** 3. row ***************************
                 id: 2
        select_type: DEPENDENT SUBQUERY
              table: tn
               type: eq_ref
      possible_keys: PRIMARY
                key: PRIMARY
            key_len: 92
                ref: j5.tms.key1
               rows: 1
              Extra: Using where
      3 rows in set (0.001 sec)
      

      Now, table "tms" uses full index scan even if it could easily use the same range access.

      Note: the issue goes away if the tables have 10K rows instead of 100K.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              psergei Sergei Petrunia
              Votes:
              1 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.