MySQL Forums
Forum List  »  Optimizer & Parser

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.

Options: ReplyQuote

Written By
April 16, 2014 02:07PM
April 18, 2014 05:19PM
April 24, 2014 08:20PM
Re: BETWEEN a DATE and a hard place
April 26, 2014 05:46AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.