Search code examples
mysqlpartitioningpruning

MySQL Partition Pruning PARTITION BY LIST

I have created a MySQL table which is partitioned by the months of a date column like this:

CREATE TABLE `my_big_table` (
  `some_id` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `some_value` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `some_other_value` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `time` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY LIST(MONTH(time))
(PARTITION p1 VALUES IN (1) ENGINE = MyISAM,
 PARTITION p2 VALUES IN (2) ENGINE = MyISAM,
 PARTITION p3 VALUES IN (3) ENGINE = MyISAM,
 PARTITION p4 VALUES IN (4) ENGINE = MyISAM,
 PARTITION p5 VALUES IN (5) ENGINE = MyISAM,
 PARTITION p6 VALUES IN (6) ENGINE = MyISAM,
 PARTITION p7 VALUES IN (7) ENGINE = MyISAM,
 PARTITION p8 VALUES IN (8) ENGINE = MyISAM,
 PARTITION p9 VALUES IN (9) ENGINE = MyISAM,
 PARTITION p10 VALUES IN (10) ENGINE = MyISAM,
 PARTITION p11 VALUES IN (11) ENGINE = MyISAM,
 PARTITION p12 VALUES IN (12) ENGINE = MyISAM)

Now, when I run

explain partitions select * from my_big_table where month(time) = 2 limit 10;

I get as output:

id  select_type table       partitions              type    possible_keys   key key_len ref rows        Extra
1   SIMPLE      my_big_table    p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12  ALL NULL        NULL    NULL    NULL    10762868    "Using where"

So apparently all partitions are being read, and I wonder why. Shouldn't the engine be clever enough to figure out that matching values can only be found in partition p2? I get the same output for a date based query, like

explain partitions select * from my_big_table where date(time) = '2013-02-01' limit 10;

Any clues?

Cheers, Alex

Solution

  • Ok, after some more googling and some more trial and error:
    The issue appears to be with the month(...) or date(...) statement in the where clause of my query. Querying

    explain partitions select * from my_big_table where time = '2013-02-01 00:00:00' limit 10;

    only scans partition p2 as expected. So it seems like partition pruning only works if the raw value of a partition column is part of the where clause.

    Cheers, Alex