MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain estimate completely wrong for range when partitioned by day
Posted by: Blake Harps
Date: February 14, 2014 02:58PM

Øystein Grøvlen Wrote:
-------------------------------------------------------
> Hi Blake,
>
> What does "EXPLAIN PARTITIONS" show for the two
> queries?


It actually shows it using all 7 of the partitions, which is surprising, as I would expect it to be limited to partition that holds the day's data.

root@local-3306 [(none)]>explain partitions SELECT count(*) as cnt FROM debuglog_test.stats_201402 _BASE WHERE _BASE.`created` BETWEEN '2014-02-14 00:00:00' AND '2014-02-14 23:59:59';
+----+-------------+-------+----------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | _BASE | p0,p1,p2,p3,p4,p5,p6 | range | created | created | 5 | NULL | 1 | Using where; Using index |
+----+-------------+-------+----------------------+-------+---------------+---------+---------+------+------+--------------------------+



Edited 1 time(s). Last edit at 02/14/2014 03:02PM by Blake Harps.

Options: ReplyQuote




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.