maria-developers team mailing list archive

[Jeremy Cole <jeremycole@xxxxxxxxxx>]

In a perfect world, EXPLAIN INSERT would tell you about rows being selected
(with share locks) for other tables to satisfy foreign key constraints, and
triggers firing... of course, this is not a perfect world.

Regards,

Jeremy


On Fri, Sep 27, 2013 at 9:09 AM, 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
>