[MDEV-5633] Better Support For Partitions/Prune By Date Created: 2014-02-08  Updated: 2023-11-03

Status: Stalled
Project: MariaDB Server
Component/s: Partitioning
Fix Version/s: None

Type: Task Priority: Major
Reporter: BELUGABEHR Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None


 Description   

I created a table based on a hash of a date:

CREATE TABLE employees (
 
    id INT NOT NULL,
 
    fname VARCHAR(30),
 
    lname VARCHAR(30),
 
    hired DATE NOT NULL DEFAULT NOW(),
 
    separated DATE NOT NULL DEFAULT '9999-12-31',
 
    job_code INT,
 
    store_id INT
 
)
 
PARTITION BY HASH( DAYOFYEAR(hired) )
 
PARTITIONS 45;

I would think that the query engine could prune searches since the hash value is, in my situation, monotonically increasing, but it does not appear to be the case. In a range search, the optimizer should be able to put the lower and upper bounds of the range into the hash function to come up with the upper and lower bounds for pruning partitions.

 Comments   
Comment by Sergei Golubchik [ 2014-02-10 ]

Hashes don't work that way. They generally don't preserve the order, so even if the function would be monotonically increasing, the hash of it wouldn't be. That's why an optimizer cannot possibly guess the range of hash values from the range of hash arguments.

Comment by BELUGABEHR [ 2014-02-12 ]

If I may:

I read from the MySQL Manual:

When PARTITION BY HASH is used, MySQL determines which partition of num partitions to use based on the modulus of the result of the user function. In other words, for an expression expr, the partition in which the record is stored is partition number N, where N = MOD(expr, num).

Given that remark, can date operators be special-cased to allow for this behavior? Given: PARTITION BY HASH( DAYOFYEAR(hired)) and the MOD of the hash function, it can be determined which range of partitions contain given date.

 
-- Return count of all employees hired in the last seven days.  Only considers seven partitions.
 
SELECT count(1) FROM employees WHERE hired >= (DAYOFYEAR(NOW()) - 7);

Comment by Sergei Golubchik [ 2014-02-12 ]

I see. Yes, to a certain extent it's possible. But it would need rather big changes in the optimizer, so we cannot implement it anytime soon.

Comment by BELUGABEHR [ 2014-02-14 ]

More formally stated:

CREATE TABLE members (
 
    firstname VARCHAR(25) NOT NULL,
 
    lastname VARCHAR(25) NOT NULL,
 
    username VARCHAR(16) NOT NULL,
 
    joined DATETIME NOT NULL
 
)
 
PARTITION BY HASH(TO_DAYS(joined))
 
PARTITIONS 7;
 

INSERT INTO members VALUES ('fred','flintstone','fflintstone','2014-01-04');
 
INSERT INTO members VALUES ('barney','rubble','brubble','2014-01-05');
 
INSERT INTO members VALUES ('homer','simpson','hsimpson','2014-01-06');
 
INSERT INTO members VALUES ('jessica','rabbit','jrabbit','2014-01-07');
 

SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS FROM information_schema.`PARTITIONS` WHERE TABLE_NAME='members';

TABLE_NAME PARTITION_NAME TABLE_ROWS
members p0 1
members p1 1
members p2 1
members p3 1
members p4 0
members p5 0
members p6 0 

EXPLAIN PARTITIONS SELECT * FROM members WHERE joined BETWEEN '2014-01-04' AND '2014-01-06';

id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE members p0,p1,p2,p3,p4,p5,p6 ALL         7 Using where

The optimizer should be able to prune out all partitions except partitions: 1,2,3

Generated at Thu Feb 08 07:05:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.