Re: BETWEEN a DATE and a hard place

[Rick James]

How many rows in this table? Really; this is an important question for the puzzle you pose.

> 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.

That inhibits the use of the "Query cache", which is turned off for PARTITIONed tables, anyway. So, if you are seeing variations in performance, it is due to other things.

100 partitions? Well, the query has to open all 100 partitions _before_ figuring out which partition(s) to use. This is a known deficiency. The main workaround is not to have so many partitions.

Please run
EXPLAIN PARTITIONS SELECT ... for each of the two forms.
It will show you whether (or not) there is any "partition pruning".

Back to my question about the number of rows. If you don't have over a million rows in the table, then PARTITIONing probably incurs more overhead than benefit.

Please try the same two queries (and EXPLAINs) for the same dataset, but with no partitioning. It may be as simple as
CREATE TABLE test (INDEX(dev_tz_date)) SELECT * FROM tbl;
to get the test table you need.

More questions that may (or may not) bear on the problem:
* Which version of MySQL?
* Is dev_tz_date indexed?
* Which ENGINE are you using? (MyISAM and InnoDB have different characteristics)

Further notes:
AND dev_tz_date BETWEEN '2014-02-01' AND '2014-02-05' -- useless for "partition pruning" unless you use BY RANGE.
AND (... OR ...) -- This _may_ be optimized to do the pruning for KEY. But if you naturally need ranges, why not simply use BY RANGE and avoid the messy SQL-construction?