I'm trying to partition a database using a DATE column to take advantage of partition pruning in MySQL 5.7. For Internal reasons, I need to partition by RANGE COLUMNS because it is easy and fast to add/drop partitions.
While the MySQL website says:
The optimizer can also perform pruning for WHERE conditions that involve comparisons of the preceding types on multiple columns for tables that use RANGE COLUMNS or LIST COLUMNS partitioning.
It also says:
This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. In addition, in MySQL 5.7, pruning can be applied for such tables when the partitioning expression uses the TO_SECONDS() function.
which seems conflicting. I'm wondering if it's possible to partition DATE columns such as:
PARTITION BY RANGE COLUMNS(date) (
PARTITION partition_2016_04_28 VALUES LESS THAN ('2016-04-28'),
PARTITION partition_2016_04_29 VALUES LESS THAN ('2016-04-29'),
PARTITION partition_2016_04_30 VALUES LESS THAN ('2016-04-30')
);
and still take advantage of partition pruning? I would like to avoid the TO_DAYS function if at all possible because it makes tables much cleaner and easier to use. Any thoughts?
I created my own tables and wrote my own queries. Using EXPLAIN PARTITIONS I found that partitioning on dates using this method does actually employ partition pruning.