Re: Partition Pruning

[Andrew McGhie]

Rick James Wrote:
-------------------------------------------------------
> pt_201005,pt_201008
>
> It is a feature/bug... NULLs go into the _first_
> partition. Queries tend to check for NULLs
> (sometimes when it should know that is
> impossible), hence the probe into pt_201005.

I figured it was a "feature" as I'd also tried adding an additional "created_dtm IS NOT NULL" predicate to the WHERE clause so the query read:

EXPLAIN PARTITIONS
  SELECT COUNT(*) 
  FROM   partitioned_table
  WHERE  created_dtm >= '2010-08-01' 
  AND    created_dtm  < '2010-09-01' 
  AND    event_dtm IS NOT NULL;

But still MySQL attempts to read from the 'pt_201005' partition. Perhaps they'll fix in in v6!?