Search code examples
mysqlpartitioning

mysql table partition issue

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';

enter image description here

enter image description here

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.

Solution

  • 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

    UPDATE

    You may create generated column which stores extracted year value and partition by it.

    DEMO