Mysql range partition with range select
<p>I couldn't find an example like mine, so here's the thing:</p>

<p>I have a big data set that I need to aggregate on top of.</p>

<p>We're talking about ~ %500M rows with a date field ranging from 2y ago until now.
My first instinct was to partition the table by this field (creating a partition on the date field), which leaves roughly 20M rows per partition.</p>

<p>Then I have indexes on the other fields I will aggregate/group by.</p>

<p>Here's my table definition (simplified for brevity sake):</p>

<pre><code>create table t1(
date_field datetime not null,
additional_id int not null,
category_id int not null,
value_field1 double,
value_field2 double,
primary key(additional_id,date_field)
)
ENGINE=InnoDB 
PARTITION BY RANGE(YEAR(date_field)*100 + MONTH(date_field)) (
PARTITION p_201411  VALUES LESS THAN (201411),
PARTITION p_201412  VALUES LESS THAN (201412),

#all the partitions until the current month...

PARTITION p_201610  VALUES LESS THAN (201610),
PARTITION p_201611  VALUES LESS THAN (201610),
PARTITION p_catchall VALUES LESS THAN MAXVALUE );
</code></pre>

<p>If I execute a query that gets a date directly, only the partition for the month is used, based on the output of  <strong>explain partitions</strong> on top of a query such as the following one:</p>

<pre><code>select value_field1 where additional_id=x and date_field='2014-11-05'
</code></pre>

<p>However, if I use a date range (even if inside the same partition), all partitions are scanned</p>

<pre><code>select value_field1 where additional_id=x and date_field&gt; '2014-11-05' and date_field &lt;'2014-11-10'
</code></pre>

<p>(Same result if I use between).</p>

<p>What am I missing here? Is this really the right way to partition this table?</p>

<p>Thanks in advance</p>