MySQL Forums
Forum List  »  Partitioning

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
2980
September 17, 2010 06:04AM
1245
September 17, 2010 06:43AM
1286
September 21, 2010 02:41PM
1314
September 21, 2010 04:33PM
1237
September 23, 2010 09:17AM
1315
September 28, 2010 10:51AM
1178
September 28, 2010 04:52PM
1701
September 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.