maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

I agree that for INSERT .. SELECT, EXPLAIN is useful - it shows query plan of 
the SELECT part.

I was wondering specifically about the INSERT ... VALUES (...) syntax. I was
just checking EXPLAIN in Maria vs EXPLAIN in MySQL, and could not find what is
the rationale for EXPLAIN INSERT ... VALUES.

On Fri, Sep 27, 2013 at 06:22:09PM +0200, Jean Weisbuch wrote:
> 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

> _______________________________________________
> 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


-- 
BR
 Sergei
--

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