mysql - Mysql8 partition by month, partition pruning not working

Question

I have a large table that I have partitioned by month

CREATE TABLE `log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `logdate` date NOT NULL,
 ...
,
  PRIMARY KEY (`id`,`logdate`)
) PARTITION BY RANGE (month(`logdate`))
(PARTITION part0 VALUES LESS THAN (2),
 PARTITION part1 VALUES LESS THAN (3),
 PARTITION part2 VALUES LESS THAN (4),
 PARTITION part3 VALUES LESS THAN (5),
 PARTITION part4 VALUES LESS THAN (6),
 PARTITION part5 VALUES LESS THAN (7),
 PARTITION part6 VALUES LESS THAN (8),
 PARTITION part7 VALUES LESS THAN (9),
 PARTITION part8 VALUES LESS THAN (10),
 PARTITION part9 VALUES LESS THAN (11),
 PARTITION part10 VALUES LESS THAN (12),
 PARTITION part11 VALUES LESS THAN MAXVALUE);

I have inserted 3 months of data and can see that the rows have been put into their respective partitions.

When I query specifying the partition, the correct data is returned and the explain shows that it is selecting from the correct partition

select logdate, sum(total) from log partition(part10) 
                where logdate between '2020-11-01' and '2020-11-30' group by 1 order by 1 desc;

However when not specifying the partition no partition pruning is occurring for the below.

select logdate, sum(total) from log 
                where logdate between '2020-11-01' and '2020-11-30' group by 1 order by 1 desc;


select logdate, sum(total) from log 
                where month(logdate) = 11 group by 1 order by 1 desc;

According to mysql-8 documentation https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html

Partitioning schemes based on time intervals. If you wish to implement a partitioning scheme based on ranges or intervals of time in MySQL 8.0, you have two options:

1. Partition the table by RANGE, and for the partitioning expression, employ a function operating on a DATE, TIME, or DATETIME column and returning an integer value - as shown here in my code

2. Partition the table by RANGE COLUMNS, using a DATE or DATETIME column as the partitioning column.

What am I missing?

question from:https://stackoverflow.com/questions/65895219/mysql8-partition-by-month-partition-pruning-not-working

Answer 1

Waitting for answers