Re: [Maria-developers] EXPLAIN INSERT in MySQL - not useful after all?

Fri, 27 Sep 2013 09:35:00 -0700

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 ([email protected]) 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 : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp 
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Reply via email to