MySQL Forums :: Partitioning :: Partition Pruning


Advanced Search

Partition Pruning
Posted by: Andrew McGhie ()
Date: September 17, 2010 06:04AM

Hello,

I'm using MySQL 5.1.50 running on Sun Sparc v9 and have a question MySQL's partition pruning strategy, I've created the following table (for the purpose of demonstrating my issue):
CREATE TABLE partitioned_table
  ( created_dtm DATETIME NOT NULL )
PARTITION BY RANGE ( TO_DAYS(created_dtm) ) (
  PARTITION pt_201005 VALUES LESS THAN ( TO_DAYS('2010-06-01') )
, PARTITION pt_201006 VALUES LESS THAN ( TO_DAYS('2010-07-01') )
, PARTITION pt_201007 VALUES LESS THAN ( TO_DAYS('2010-08-01') )
, PARTITION pt_201008 VALUES LESS THAN ( TO_DAYS('2010-09-01') )
, PARTITION pt_201009 VALUES LESS THAN ( TO_DAYS('2010-10-01') )
);

I then populate the table with some test data:

INSERT INTO partitioned_table VALUES ( '2010-01-01' ), ( '2010-02-01' ), ( '2010-03-01' ), ( '2010-04-01' ), ( '2010-05-01' ), ( '2010-06-01' ), ( '2010-07-01' ), ( '2010-08-01' ), ( '2010-09-01' );

I check that the data has been stored in the correct partition(s):
SELECT table_name
     , partition_name
     , partition_description
     , date_add('0000-01-01', INTERVAL partition_description-1 DAY) AS less_than
     , table_rows 
FROM   information_schema.partitions 
WHERE  table_schema = 'test' 
AND    table_name   = 'partitioned_table';

+-------------------+----------------+-----------------------+------------+------------+
| table_name        | partition_name | partition_description | less_than  | table_rows |
+-------------------+----------------+-----------------------+------------+------------+
| partitioned_table | pt_201005      | 734289                | 2010-06-01 |          5 |
| partitioned_table | pt_201006      | 734319                | 2010-07-01 |          1 |
| partitioned_table | pt_201007      | 734350                | 2010-08-01 |          1 |
| partitioned_table | pt_201008      | 734381                | 2010-09-01 |          1 |
| partitioned_table | pt_201009      | 734411                | 2010-10-01 |          1 |
+-------------------+----------------+-----------------------+------------+------------+

When I 'explain' the following query why does it state that it wants to query the pt_201005 partition in addition to the pt_201008 partition?

EXPLAIN PARTITIONS 
  SELECT COUNT(*) 
  FROM   partitioned_table
  WHERE  created_dtm >= '2010-08-01' 
  AND    created_dtm  < '2010-09-01';
+----+-------------+-------------------+-----------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table             | partitions            | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-----------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | partitioned_table | pt_201005,pt_201008   | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------------------+-----------------------+------+---------------+------+---------+------+------+-------------+

Options: ReplyQuote


Subject Views Written By Posted
Partition Pruning 2922 Andrew McGhie 09/17/2010 06:04AM
Re: Partition Pruning 1231 laptop alias 09/17/2010 06:43AM
Re: Partition Pruning 1273 Shawn Taylor 09/21/2010 02:41PM
Re: Partition Pruning 1301 Rick James 09/21/2010 04:33PM
Re: Partition Pruning 1212 Andrew McGhie 09/23/2010 09:17AM
Re: Partition Pruning 1303 Mattias Jonsson 09/28/2010 10:51AM
Re: Partition Pruning 1166 Rick James 09/28/2010 04:52PM
Re: Partition Pruning 1686 Mattias Jonsson 09/29/2010 04:49AM


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.