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 <pser...@askmonty.org> 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@lists.launchpad.net > >Unsubscribe : https://launchpad.net/~maria-developers > >More help : https://help.launchpad.net/ListHelp > _______________________________________________ > Mailing list: https://launchpad.net/~maria-developers > Post to : maria-developers@lists.launchpad.net > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp