MySQL Forums
Forum List  »  Optimizer & Parser

Re: BETWEEN a DATE and a hard place
Posted by: Gerald Bayles
Date: April 28, 2014 07:27AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
2032
April 16, 2014 02:07PM
1070
April 18, 2014 05:19PM
1104
April 24, 2014 08:20PM
1050
April 26, 2014 05:46AM
Re: BETWEEN a DATE and a hard place
1095
April 28, 2014 07:27AM


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.