MySQL Forums
Forum List  »  Partitioning

Issue with Partitioning
Posted by: sreenath kn
Date: May 30, 2022 01:55AM

I have done some tests to study about partitioning , so that I can implement it to my live database that is having large data.
Step 1:

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(year(purchased))
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

Setp 2:

INSERT INTO tr VALUES
(1, 'Store1', '2003-10-15'),
(2, 'Store1', '1997-11-05'),
(3, 'Store1', '2009-03-10'),
(4, 'Store2', '1989-01-10'),
(5, 'Store1', '2014-05-09'),
(6, 'Store1', '1987-06-05'),
(7, 'Store3', '2011-11-22'),
(8, 'Store3', '1992-08-04'),
(9, 'Store4', '2006-09-16'),
(10, 'Store4', '1998-12-25');

Step 3:

EXPLAIN SELECT * FROM tr WHERE purchased BETWEEN '1987-06-05' AND '1989-01-10';

in this step when am retriving data it only using p0 partition which is correct.

Setp 4:

EXPLAIN SELECT * FROM tr WHERE YEAR(purchased) BETWEEN 1987 AND 1989;

in this step I just changed the where query to check the data based on year only not a full date.Here in this case the query searching in whole partition.
I don't understand why it is working like this.Please explain if there is any mistake so that i can update my steps and complete the work.

Options: ReplyQuote


Subject
Views
Written By
Posted
Issue with Partitioning
80
May 30, 2022 01:55AM
45
June 05, 2022 07:48PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.