Re: Explain estimate completely wrong for range when partitioned by day

[Rick James]

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:
http://mysql.rjweb.org/doc.php/partitionmaint