Issue with Partitioning
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.