Could it be of use for the INSERT ... SELECT ... queries? 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

It looks like a bug. The partition pruning kicks just fine for UPDATEs show create table t2p\G *************************** 1. row ***************************        Table: t2p Create Table: CREATE TABLE `t2p` (   `a` int(11) DEFAULT NULL,   `b` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION p001 VALUES LESS THAN (11) ENGINE = InnoDB,  PARTITION p002 VALUES LESS THAN (21) ENGINE = InnoDB,  PARTITION p003 VALUES LESS THAN (31) ENGINE = InnoDB,  PARTITION p004 VALUES LESS THAN (41) ENGINE = InnoDB,  PARTITION p005 VALUES LESS THAN (51) ENGINE = InnoDB,  PARTITION p006 VALUES LESS THAN (61) ENGINE = InnoDB,  PARTITION p007 VALUES LESS THAN (71) ENGINE = InnoDB,  PARTITION p008 VALUES LESS THAN (81) ENGINE = InnoDB,  PARTITION p009 VALUES LESS THAN (91) ENGINE = InnoDB,  PARTITION p010 VALUES LESS THAN (101) ENGINE = InnoDB) */ 1 row in set (0.01 sec) explain partitions update   t2p set b = 38 where a = 30\G *************************** 1. row ***************************            id: 1   select_type: UPDATE         table: t2p    partitions: p003          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 2         Extra: Using where explain partitions insert into t2p set a=45, b = 20 \G *************************** 1. row ***************************            id: 1   select_type: INSERT         table: t2p    partitions: p001,p002,p003,p004,p005,p006,p007,p008,p009,p010          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: NULL         Extra: NULL On September 27, 2013 at 09:18:19 , 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