Partition Pruning uncovered

The partitioning development for 5.1 is close to its completion (apart from the
stabilisation phase). An important part of this development is the ability to
prune away partitions not used in the query. Here follows some guidelines how
the optimiser handles this at the moment.

For most functions the optimizer can only handle equality conditions thus:
where partition_field = 2
or
where partiiton_field IN (2,3,4)

In this case the optimiser can use this independent of what the partition
function is. It will do so by actually applying the partition function on the
equal value thus:
partition_function(2) = partition 3
partition_function(3) = partition 5
partition_function(4) = partition 3

It will set a bit in partition_used bitmap to indicate which partitions are used
to indicate to the executer which partitions to scan.

So this will work for all partition functions.

Range optimisations will work for the following partition functions using
RANGE or LIST partitioning

PARTITION BY X(f(partition_field)) where f(partition_field) is:
f(partition_field) = partition_field
f(partition_field) = year(field)
f(partition_field) = FUNC_TO_DAYS(partition_field)
(if partition_field is of date type or converted to in last two examples)

Range optimisations will also work for all partition functions and all
partitioning in the following case: When the range is either of type a list
of values (1,3,5) or where the range is a short range e.g. BETWEEN (3,5)
which is simply converted to (3,4,5).

For Range partitions the actual range of partitions is found by applying
partition_function(start_range_value) = partition 3
partition_function(end_range_value) = partition 5
=> Scan partition 3,4 and 5

For LIST partitions one will do it slightly different
partition_function(start_range_value) = x
partition_function(end_range_value) = y
(The partition functions used here must be increasing or decreasing)
Then scan all lists values from x to y (it is kept in a sorted list to enable quick
fetch of a partition given a value) and for each list value found mark the
partition as used.

For Range types that will work for all partition types the partition function will
be evaluated for each value in the interval and its corresponding partition will
be marked for use.

These optimisations apply also to subpartition so you can have conditions on
only subpartition fields and you will get pruning for subpartitions only and vice
versa for partitions and also when conditions on both at the same time.

So to use an example

CREATE TABLE t1 (a date)
PARTITION BY RANGE(year(a))
(PARTITION p0 VALUES LESS THAN (1995), ,,,
PARTITION p9 VALUES LESS THAN (2004));

SELECT * from t1 WHERE a >= '1995-01-01 AND a <= '1997-12-31';

Here we have a range optimised function =>
year(start_range_value) = year('1995-01-01') = 1995 => p0
year(end_range_value) = year('1997-12-31') = 1997 => p2

Thus p0 - > p2 will be scanned, thus p0, p1 and p2.

If we replace RANGE by LIST only and all else is kept constant then
we will scan list values from 1995 to 1997 and find that p0, p1 and p2
is to be scanned.

For HASH partitioned variants there will not be any optimisations in this
case due to that the interval is to wide since the data type of the equal
condition is date and there more than a thousand dates in the range.
However if the user makes a slight change to the data model and instead
use the following:

CREATE TABLE t1 (a_year int)
PARTITION BY HASH(any_function(a_year))
PARTITIONS 10;

SELECT * from t1 WHERE a_year >= 1995 AND a_year <= 1997;

Then the range [1995,1997] will always be good enough for partition pruning.
And thus a maximum of 3 partitions will be scanned in the query.