maria-developers team mailing list archive

[Jean Weisbuch <jean@xxxxxxxxxx>]

Could it be of use for the INSERT ... SELECT ... queries?

Sergei Petrunia <psergey@xxxxxxxxxxxx> wrote:
>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
>
>
>
>_______________________________________________
>Mailing list: https://launchpad.net/~maria-developers
>Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
>Unsubscribe : https://launchpad.net/~maria-developers
>More help   : https://help.launchpad.net/ListHelp