Re: BETWEEN a DATE and a hard place
Posted by:
Rick James
Date: April 26, 2014 05:46AM
I welcome your "contrary" comments. But I stand my ground; here is a discussion of why.
Case 1 probably involves a subtle issue with dates. In reality, it is checking the right date, plus the first partition. This is because invalid dates map to NULL; NULLs are located in the first partition.
Case 3 may be the same as case 1, but you happened to land in the "first" partition.
Case 2 hides the likely use case: a range of days. Once you ask for a range of days, pruning becomes impossible (at least in the current implementation).
I consider the NULL problem of dates to be a minor issue that is best dealt with by including an empty 'first' partition.
(You could modify your tests to have 3 partitions, and make sure the SELECT is not hitting the first partition, to validate my claims. I would expect the desired partition, plus the first partition to be hit in cases 1 and 3.)
If you are only reaching in by _one_ date, pruning is feasible in many schemes (BY RANGE, BY KEY, etc). But, unless there is some other reason for PARTITIONing, you may as well skip partitioning and have the partition key as the first part of the index.
Your Case 2 is simplistic enough to argue for INDEX(day_of_year) in place of PARTITIONing.
Feel free to enhance your cases to make your point. I expect that I will be able to poke holes in the new cases. If not, I may learn something!
What version are you using? There have been some improvements in recent versions that impact the subtle cases you are bringing up.