MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain estimate completely wrong for range when partitioned by day
Posted by: Rick James
Date: February 15, 2014 12:42PM

This is another example of why RANGE partitioning is nearly the only useful one for pruning.

The optimizer does not have the smarts to realize that all possible values in that range would map to one partition. Here are the characteristics that conspire:
* PARTITION BY (some-function-call)
* WHERE some-range

The TO_DAYS() function is special cased. Maybe a few others are, but certainly not all functions.

What is your goal? To have 7 partitions and rotate among them? Will you be deleting old data before cycling around? If so, you need to DROP and recreate a day and do it early, thereby leaving you with only 6 days of data. If are not deleting, then the partitions will be clogged with miscellany rows, and there will be no advantage to partitioning.

If you want 7 days of data, suggest having 10 RANGE partitions and maintain them as described here:

Options: ReplyQuote

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.