Partition pruning is not correct for partition by range columns (Doc ID 1538113.1)

Last updated on MAY 05, 2016

Applies to:

MySQL Server - Version 5.5 to 5.7 [Release 5.5 to 5.7]
Information in this document applies to any platform.

Symptoms

The explain plan shows that 2 partitions are selected while the interval only matches one partition.

Example:

mysql> CREATE TABLE t(
id int AUTO_INCREMENT,
dt datetime,
PRIMARY KEY (dt,id),
UNIQUE KEY (id,dt)
) ENGINE=InnoDB AUTO_INCREMENT=3069638276 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(dt)
(
 PARTITION p20130101 VALUES LESS THAN ('2013-01-01'),
 PARTITION p20130115 VALUES LESS THAN ('2013-01-15'),
 PARTITION p20130201 VALUES LESS THAN ('2013-02-01'),
 PARTITION p20130215 VALUES LESS THAN ('2013-02-15'),
 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

mysql> EXPLAIN PARTITIONS SELECT count(*) FROM t WHERE dt >= '2013-01-15' AND dt < '2013-02-01'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: p20130201,p20130215
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 9
          ref: NULL
         rows: 2
        Extra: Using where; Using index
1 row in set (0.00 sec)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms