maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

Hi,

Among the topics of yesterday's optimizer call was the question of what is the
point of EXPLAIN INSERT. The point was that INSERT doesn't need a query plan.
Somebody has mentioned that perhaps, EXPLAIN INSERT could show results of
partition pruning.

This doesn't seem to be the case:


5.6.14-debug
MySQL [test]> explain partitions insert into t1p values (2,2);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------------+
1 row in set (1.34 sec)


5.7.2-m12-debug:
MySQL [test]> explain partitions insert into t1p values (1,1);
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
|  1 | INSERT      | t1p   | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)


That is, EXPLAIN in 5.7 looks a bit more meaningful, but it still doesn't show 
results of partition pruning.

The table t1p is partitioned, it was created as follows:

create table t1(a int);
insert into t1 values (1),(2),(3),(4);
create table t1p (a int, b int) partition by hash(a) partitions 4;
insert into t1p select * from t1;

that is, values (1,1) should have allowed to pick the partition to use, or it
wasn't done.

So, is EXPLAIN INSERT meaningles, or I am missing something here?


BR
 Sergei
--

Sergei Petrunia, Software Developer
MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog