Re: Partition pruning when partition expression contains a function

[Mikael Ronström]

Hi,
There are range optimisations for LIST partitions implemented and there is also
a special range optimisation for hash partitions.

The range optimisations is an interesting mathematical problem. You'll find a
number of comments in the MySQL Server code that explains the optimisations
that are employed.

DAYOFWEEK has a problem in that it isn't strictly increasing or decreasing over
date. Thus DATE > 'x' AND DATE < 'y' doesn't really give much help on knowing
which partition the data is in. DAYOFWEEK(date) > 'monday' AND DAYOFWEEK(date) <
'thursday' would. This will most likely be supported at the same time as MySQL
supports function indexes.

Rgrds Mikael

Partha Dutta wrote:
> What about non range partitions (such as LIST).
> Any plans on optimizations for other functions,
> such as DAYOFWEEK, etc? It would be very nice to
> partition based on datetime columns, and the most
> useful partitions are by day, month, hour,
> etc...
>

Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog: http://mikaelronstrom.blogspot.com