maria-developers team mailing list archive

[Giuseppe Maxia <g.maxia@xxxxxxxxx>]

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