MySQL Forums
Forum List  »  Optimizer & Parser

Re: BETWEEN a DATE and a hard place
Posted by: Gerald Bayles
Date: April 25, 2014 06:36AM

Thanks, I found some very useful stuff on that link.

However, I hate to be contrary and may be overly cautious (newbie to PARTITIONing) about how functions (MOD, DAYOFYEAR, etc.) work with partitioning but I find no practical advantage to RANGE over KEY partitions despite your assertion (without evidence) that "PARTITION BY RANGE is the only useful method".


Please review these query tests using various partitioning schemes:
#####Test 1: RANGE PARTITIONS ON FUNCTION...FAIL
DROP TABLE IF EXISTS blahblah;
CREATE TABLE blahblah(
id int(11) NOT NULL AUTO_INCREMENT,
date_date DATE,
PRIMARY KEY (id,date_date))
PARTITION BY RANGE(DAYOFYEAR(date_date)) (
PARTITION p0 VALUES LESS THAN (183),
PARTITION p1 VALUES LESS THAN (367)
);
#RESULTS OF QUERY LIE WITHIN ONLY ONE PARTITION (p0)
EXPLAIN PARTITIONS SELECT * FROM blahblah WHERE DAYOFYEAR(date_date) = 11\G
# ...partitions: p0,p1 =====>SEARCH BOTH PARTITIONS? WHY?

#####Test 2: RANGE PARTITIONS ON DISCRETE VALUE...PASS
DROP TABLE IF EXISTS blahblah;
CREATE TABLE blahblah(
id int(11) NOT NULL AUTO_INCREMENT,
date_date DATE,
day_of_year INT,
PRIMARY KEY (id,day_of_year))
PARTITION BY RANGE(day_of_year) (
PARTITION p0 VALUES LESS THAN (183),
PARTITION p1 VALUES LESS THAN (367)
);
#RESULTS OF QUERY LIE WITHIN ONLY ONE PARTITION (p0)
EXPLAIN PARTITIONS SELECT * FROM blahblah WHERE day_of_year = 11\G
# ...partitions: p0 =====>SEARCH ONLY ONE PARTITION! WHY NOT!

#####Test 3: KEY PARTITIONING ON DISCRETE VALUE...PASS
DROP TABLE IF EXISTS blahblah;
CREATE TABLE blahblah(
id int(11) NOT NULL AUTO_INCREMENT,
date_date DATE,
PRIMARY KEY (id,date_date))
PARTITION BY KEY(date_date) PARTITIONS 2;
#RESULTS OF QUERY LIE WITHIN ONLY ONE PARTITION(p0 or p1)
EXPLAIN PARTITIONS SELECT * FROM blahblah WHERE date_date = '2014-02-02'\G
# ...partitions: p0 =====>SEARCH ONLY ONE PARTITION! WHY NOT!


Thanks!

Options: ReplyQuote


Subject
Views
Written By
Posted
2032
April 16, 2014 02:07PM
1070
April 18, 2014 05:19PM
1105
April 24, 2014 08:20PM
Re: BETWEEN a DATE and a hard place
1261
April 25, 2014 06:36AM
1050
April 26, 2014 05:46AM


Sorry, you can't reply to this topic. It has been closed.

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.