[MDEV-22377] Subquery in an UPDATE query uses full scan instead of range - Jira

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.

relates to

MDEV-22415 Single table UPDATE/DELETE doesn't use non-semijoin Materialization

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

MariaDB Server