Re: BETWEEN a DATE and a hard place

[Gerald Bayles]

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!