Partition Pruning

[Andrew McGhie]

Hello,

I initially posted this in the wrong forum - I didn't realise there was a forum dedicated to partitioning.

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 |
+----+-------------+-------------------+-----------------------+------+---------------+------+---------+------+------+-------------+

Also is there such a concept of LOCAL indexes on a Partitioned table as there is in Oracle?