MySQL Forums
Forum List  »  Partitioning

Re: Query problem with date partitioning
Posted by: Phil Hildebrand
Date: May 22, 2008 09:08PM

It's most likely because you are using a 'Hash' partition.

Hash partitioning will end up with 17 buckets where the to_days(localdatetime)mod17 determines the partition that the row drops into.

When using the '=', the optimizer knows exactly what partition the function evaluates to because it can run the number through the function, where as the > could return many rows, and with only 17 partitions, chances are you will have rows that end up in most or all of the partitions, thus it would make sense that the optimizer would not be able to 'prune' out any of the partitions. The optimizer can't evaluate which partition it's in without the actual value (and it won't know how many values with >=).

If you used a range partition, however:

partition by range (to_days(localdatetime))
( partition pyesterday values < 733548,
partition ptommorow values < 733549,
partition pforever values less than maxvalue

you could look for localdatetime >= now(), and only ptommorow and pforever should get scanned.

Options: ReplyQuote

Written By
Re: Query problem with date partitioning
May 22, 2008 09:08PM

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.