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
• Status: Won't fix
• Category: MySQL Server: Partitions
• Severity: S4 (Feature request)
• Version: 6.0
• OS: Any
• CPU Architecture: Any
• Tags: partitioning, pruning

[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] MySQL Verification Team

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.