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

Materialization strategy is not used for REPLACE ... SELECT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.15
    • 10.0.17
    • Optimizer
    • None

    Description

      Materialization strategy is not used for REPLACE ... SELECT. Filing this based on CSC#8345.

      create table t1 (a int, b int, c int);
      insert into t1 select a,a,a from test.one_k A;
       
      create table t2 (a int, b int, c int);
       
      insert into t2 select A.a, A.a, A.a from test.one_k A;
      insert into t2 select * from t2;
      insert into t2 select * from t2;
      insert into t2 select * from t2;
      insert into t2 select * from t2;

      Let's make a query with a subquery that is better executed with Materialization:

      explain
      select * from t1 where (a,b) in (select max(a),b from t2 group by b);
      +------+--------------+-------------+--------+---------------+--------------+---------+-------------------+-------+-----------------+
      | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref               | rows  | Extra           |
      +------+--------------+-------------+--------+---------------+--------------+---------+-------------------+-------+-----------------+
      |    1 | PRIMARY      | t1          | ALL    | NULL          | NULL         | NULL    | NULL              |  1000 | Using where     |
      |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 8       | j12.t1.a,j12.t1.b |     1 |                 |
      |    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL         | NULL    | NULL              | 16000 | Using temporary |
      +------+--------------+-------------+--------+---------------+--------------+---------+-------------------+-------+-----------------+

      Ok.
      Now, let's run a REPLACE ... SELECT for it:

      create table t3 as select * from t1 limit 1;
       
      replace into t3
      select * from t1 where (a,b) in (select max(a),b from t2 group by b);
       
      # Query OK, 1000 rows affected (3 min 30.18 sec)
      # Records: 1000  Duplicates: 0  Warnings: 0

      3 min looks like it is too long for the above EXPLAIN plan. Indeed, SHOW EXPLAIN shows:

      MariaDB [(none)]> show explain for 2;
      +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------------+
      | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows  | Extra           |
      +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------------+
      |    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |  1000 | Using where     |
      |    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL | 15794 | Using temporary |
      +------+--------------------+-------+------+---------------+------+---------+------+-------+-----------------+

      It is using IN->EXISTS conversion, not materialization.

      CREATE TABLE ... SELECT is not affected:

      MariaDB [j12]> create table tmp1 as select * from t1 where (a,b) in (select max(a),b from t2 group by b);
      Query OK, 1000 rows affected (0.84 sec)
      Records: 1000  Duplicates: 0  Warnings: 0

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.