mysql table partition issue-CodePudding

I am trying to understand mysql table partitioning, below are details and example which i tried.

mysql version - 8.0.27

CREATE TABLE employees(
   id INT NOT NULL,
   fname VARCHAR(30),
   lname VARCHAR(30),
   hired DATE NOT NULL DEFAULT '1970-01-01',
   separated DATE NOT NULL DEFAULT '9999-12-31',
   job_code INT,
   store_id INT
)
ENGINE=INNODB
PARTITION BY RANGE(YEAR(separated))(
   PARTITION p0 VALUES LESS THAN(1991),
   PARTITION p1 VALUES LESS THAN(1996),
   PARTITION p2 VALUES LESS THAN(2001),
   PARTITION p3 VALUES LESS THAN MAXVALUE
);

Sample insert statement.

insert into employees values (1,'ron', 'nath', '2019-10-10', '2021-10-12',123,123);
insert into employees values (2,'ram', 'nath', '1991-10-10', '1999-10-12',123,123);

Verified data loaded in partition.

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='employees';

This shows data in two partition p2 and p3. But when i run below select statement and do explain, i see all the partition being referred.

EXPLAIN select * from employees where year(separated)='1999';

As you can see in both screen shots, All partitions are referred, not sure why? I am expected only specific partition to be referred when where clause filter applied to fetch data from specific partition.

CodePudding user response：

The function in WHERE does not allow to use partition pruning. The fact that the expression in WHERE matches the partitioning expression is not taken into account.

Use

select * from employees where separated BETWEEN '1999-01-01' AND '1999-12-31';

DEMO

CodePudding user response：

where year(separated)='1999';

separated is hidden in a function call, hence not sargable . Akina elaborates on it.

I suggest

WHERE separated >= '1999-01-01'
  AND separated  < '1999-01-01'   INTERVAL 1 YEAR

But... The non-partitioned equivalent table will let that Select run just as fast. (It would need INDEX(separated).) So, I ask what are you hoping to gain by Partitioning?

A common reason for Partitioning a time series is to make the deletion of 'old' data faster. This can be done via DROP PARTITION. details