Bug #75474 Problem with subpartitions

• Submitted: 10 Jan 2015 1:56
• Modified: 14 Jan 2015 10:06
• Reporter: William Chiquito
• Status: Not a Bug
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.5/5.6
• OS: Any
• CPU Architecture: Any
• Tags: subpartitions

[10 Jan 2015 1:56] William Chiquito

Description:
Expect only the p1_p1sp1 and p1_p1sp2 subpartitions will be scanned, but unlike a scan of all subpaticiones done.

How to repeat:
DROP TABLE `testtable`;

DROP TABLE IF EXISTS `testtable`;

CREATE TABLE IF NOT EXISTS `testtable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `purchased` DATE NOT NULL,
  KEY (`id`),
  KEY (`purchased`)
)
PARTITION BY RANGE (YEAR(`purchased`))
SUBPARTITION BY HASH (DAYOFYEAR(`purchased`))
SUBPARTITIONS 5 (
  PARTITION p0 VALUES LESS THAN (2015),
  PARTITION p1 VALUES LESS THAN (2016)
);

INSERT INTO `testtable` (`purchased`)
VALUES
('2014-12-27'), ('2014-12-28'), ('2014-12-29'),
('2014-12-30'), ('2014-12-31'), ('2015-01-01'),
('2015-01-02'), ('2015-01-03'), ('2015-01-04'),
('2015-01-05');

EXPLAIN PARTITIONS
SELECT `id`, `purchased`
FROM `testtable`
WHERE `purchased` BETWEEN '2015-01-02' AND '2015-01-03';

+----+-------------+-----------+----------------------------------------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table     | partitions                                   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-----------+----------------------------------------------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | testtable | p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3,p1_p1sp4 | range | purchased     | purchased | 8       | NULL |    5 | Using where |
+----+-------------+-----------+----------------------------------------------+-------+---------------+-----------+---------+------+------+-------------+

[13 Jan 2015 19:51] MySQL Verification Team

Thank you for the bug report.

http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html

" The optimizer can perform pruning whenever a WHERE condition can be reduced to either one of the following two cases:

    partition_column = constant

    partition_column IN (constant1, constant2, ..., constantN) "

mysql 5.6 > EXPLAIN PARTITIONS
    -> SELECT `id`, `purchased`
    -> FROM `testtable`
    -> WHERE `purchased` IN ('2015-01-02', '2015-01-03');
+----+-------------+-----------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | partitions        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testtable | p1_p1sp2,p1_p1sp3 | ALL  | purchased     | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-----------+-------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

[14 Jan 2015 10:06] William Chiquito

The documentation also says: "Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning.".

And it's true, because eliminating subpartitions, everything works as expected:

DROP TABLE IF EXISTS `testtable`;

CREATE TABLE IF NOT EXISTS `testtable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `purchased` DATE NOT NULL,
  KEY (`id`),
  KEY (`purchased`)
)
PARTITION BY RANGE (YEAR(`purchased`))
/*SUBPARTITION BY HASH (DAYOFYEAR(`purchased`))
SUBPARTITIONS 5 */(
  PARTITION p0 VALUES LESS THAN (2015),
  PARTITION p1 VALUES LESS THAN (2016)
);

INSERT INTO `testtable` (`purchased`)
VALUES
('2014-12-27'), ('2014-12-28'), ('2014-12-29'),
('2014-12-30'), ('2014-12-31'), ('2015-01-01'),
('2015-01-02'), ('2015-01-03'), ('2015-01-04'),
('2015-01-05');

EXPLAIN PARTITIONS
SELECT `id`, `purchased`
FROM `testtable`
WHERE `purchased` BETWEEN '2015-01-02' AND '2015-01-03';

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | testtable | p1         | range | purchased     | purchased | 3       | NULL |    2 | Using where |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+-------------+

In both examples the condition is satisfied (as the documentation says): "This optimization is used only if the range size is smaller than the number of partitions.".

It therefore seems a specific topic with subpartitions.