MySQL Forums
Forum List  »  Partitioning

Partition Pruning
Posted by: Andrew McGhie
Date: September 17, 2010 07:18AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition Pruning
2843
September 17, 2010 07:18AM
1401
September 17, 2010 07:23AM
1381
September 18, 2010 04:13PM
1335
September 28, 2010 10:59AM


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.