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