Bug #45350 Support partition pruning for DAY() and MONTH() functions
Submitted: 5 Jun 2009 13:04 Modified: 2 Dec 2009 13:47
Reporter: Alan Griffiths Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:6.0 OS:Any
Assigned to:
Tags: partitioning, pruning
Triage: Needs Triage: D5 (Feature request)

[5 Jun 2009 13:04] Alan Griffiths
Description:
Hi,

As stated here http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html pruning on datetime fields only works if the partitioning function is YEAR() or TO_DAYS(). I would like to request that DAY() and MONTH() be added to this list.

In order to support partitioning by day/month I have been forced to encode day/month as separate integer fields and partition on those fields. This means I am wasting space and it makes my SELECTs more complex.

Thanks,

Alan

How to repeat:
Create table with datetime field, dtime,  then partition by DAY(dtime).

If you perform EXPLAIN PARTITIONS SELECT where your constraint is on the dateime field the optimiser will search all partitions.

Suggested fix:
Add pruning support for MONTH() and DAY().
[5 Jun 2009 13:52] Miguel Solorzano
Thank you for the bug report.
[2 Dec 2009 13:47] Mattias Jonsson
Since DAY() and MONTH() is cyclic it will not be implemented.
[5 Sep 2013 3:54] Mattias Jonsson
To clarify the above statement:
exact search "WHERE date_col = '2013-09-05'" will prune partitions, but a range search "WHERE date_col BETWEEN '2013-09-05' AND '2014-02-01'" will not prune.