MySQL Forums
Forum List  »  Optimizer & Parser

Re: BETWEEN a DATE and a hard place
Posted by: Rick James
Date: April 18, 2014 05:19PM

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?

Options: ReplyQuote

Written By
April 16, 2014 02:07PM
Re: BETWEEN a DATE and a hard place
April 18, 2014 05:19PM
April 24, 2014 08:20PM
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.