BETWEEN a DATE and a hard place

[Gerald Bayles]

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.