Re: Explain estimate completely wrong for range when partitioned by day

[Blake Harps]

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