MySQL Forums
Forum List  »  Optimizer & Parser

Advanced Search
New Topic

Re: Explain estimate completely wrong for range when partitioned by day
Posted by: Mattias Jonsson
Date: February 17, 2014 04:29PM

Hi Blake,

Only TO_SECONDS/TO_DAYS/YEAR() and UNIX_TIMESTAMP() functions are supported for range optimizations. That is why you see all partitions used for PARTITION BY (WEEKDAY()).

http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-functions.html

I would also suggest you to have RANGE or RANGE COLUMNS partitioning instead, as Rick proposed.

The reason for the statistics being wrong is also due to missing pruning. To avoid excessive time spent in the optimizing phase for partitioned tables (doing index dives for each partition and each index) only the largest used partitions will be checked for estimates.

Regards
Mattias

Navigate: Previous Message• Next Message
Options: Reply• Quote




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.