Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.15
-
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
- relates to
-
-
- Closed
-