Re: BETWEEN a DATE and a hard place
Thanks for your perseverance.
Server version: 5.5.37-0ubuntu0.12.04.1-log (Ubuntu)
I had my sys admin try to upgrade to 5.6.17 (or whatever the latest release is). The installation worked but 5.6.* failed in operation. We're a tiny company and don't have the wherewithal to address this now.
I modified the test cases: made all cols NOT NULL, increased to 3 partitions, and used conditions in the EXPLAIN that lie within p1. The results are consistent with what I found with the earlier test. However, I will defer to your better understanding of RANGE & KEY partitioning and use RANGE (although from my myopic perspective, I'd say KEY works perfectly and a great feature of MySQL).
My only outstanding question, then, is how could I get Test 1 to work (RANGE using function)? In reality, we're not storing a DATE but a TIMESTAMP and if I can use a function I won't need an extra column containing only the TIMESTAMP's day of year.
#####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 NOT NULL,
PRIMARY KEY (id,date_date))
PARTITION BY RANGE(DAYOFYEAR(date_date)) (
PARTITION p0 VALUES LESS THAN (122),
PARTITION p1 VALUES LESS THAN (244),
PARTITION p2 VALUES LESS THAN (367)
);
#RESULTS OF QUERY LIE WITHIN ONLY ONE PARTITION
EXPLAIN PARTITIONS SELECT * FROM blahblah WHERE DAYOFYEAR(date_date) = 200\G
# ...partitions: p0,p1,p2 =====>SEARCH ALL 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 NOT NULL,
day_of_year INT NOT NULL,
PRIMARY KEY (id,day_of_year))
PARTITION BY RANGE(day_of_year) (
PARTITION p0 VALUES LESS THAN (122),
PARTITION p1 VALUES LESS THAN (244),
PARTITION p2 VALUES LESS THAN (367)
);
#RESULTS OF QUERY LIE WITHIN ONLY ONE PARTITION
EXPLAIN PARTITIONS SELECT * FROM blahblah WHERE day_of_year = 200\G
# ...partitions: p1 =====>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 NOT NULL,
PRIMARY KEY (id,date_date))
PARTITION BY KEY(date_date) PARTITIONS 3;
#RESULTS OF QUERY LIE WITHIN ONLY ONE PARTITION
EXPLAIN PARTITIONS SELECT * FROM blahblah WHERE date_date = '2014-03-02'\G
# ...partitions: p1 =====>SEARCH ONLY ONE PARTITION! WHY NOT!