MySQL Forums
Forum List  »  Optimizer & Parser

BETWEEN a DATE and a hard place
Posted by: Gerald Bayles
Date: April 16, 2014 02:07PM

In a query I am trying to optimize, I get very different performance on the same data using two different ways to express a DATE range (dev_tz_date DATE NOT NULL).

AND (
dev_tz_date = '2014-02-01'
OR dev_tz_date = '2014-02-02'
OR dev_tz_date = '2014-02-03'
OR dev_tz_date = '2014-02-04'
OR dev_tz_date = '2014-02-05'
)

-is 3+ times faster than-

AND dev_tz_date BETWEEN '2014-02-01' AND '2014-02-05'


The former (with all the 'OR's) runs 3 times faster than the latter ('BETWEEN').
I can pretty much guess what's going on but I didn't expect this.

There are 100 partitions in the table, 'PARTITION BY KEY (dev_tz_date) PARTITIONS 100'.

I use SQL_NO_CACHE but I find that does not work as it should, so I've taken to restarting MYSQL between running each query.

Since this particular query, when implemented, will execute via PREPARE and the days span no more than a week, I'll simply add up to 7 using the ' OR dev_tz_date =' style but I'd rather have more versatile code using BETWEEN.

Options: ReplyQuote


Subject
Views
Written By
Posted
BETWEEN a DATE and a hard place
2174
April 16, 2014 02:07PM
1152
April 18, 2014 05:19PM
1195
April 24, 2014 08:20PM
1155
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.