MySQL Forums
Forum List  »  Partitioning

about SUBPARTITIONING BEHAVIOR ...
Posted by:
Date: August 18, 2010 07:46AM

Hello,

I\'ve tried your example with ASTERISK CALLs CDR database. I\'ve splited data by year and quarter.

CREATE TABLE `cdr` (
`date` datetime NOT NULL,
`src` varchar(80) CHARACTER SET latin1 NOT NULL,
`dst` varchar(80) CHARACTER SET latin1 NOT NULL,
`billingcode` varchar(256) CHARACTER SET latin1 NOT NULL,
`country` varchar(100) NOT NULL,
`type` varchar(100) NOT NULL,
`operator` varchar(50) NOT NULL,
`area` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`duration` int(11) NOT NULL,
`price` decimal(15,3) NOT NULL,
UNIQUE KEY `UNIQUE` (`date`,`src`,`dst`),
KEY `INDEX_DATE` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(date))
SUBPARTITION BY HASH (QUARTER(date))
(PARTITION p2010 VALUES LESS THAN (2011)
(SUBPARTITION q1 ENGINE = InnoDB,
SUBPARTITION q2 ENGINE = InnoDB,
SUBPARTITION q3 ENGINE = InnoDB,
SUBPARTITION q4 ENGINE = InnoDB)) */

To check that MySQL has done the job, I exec the EXPLAIN query below

EXPLAIN PARTITIONS SELECT count( * )
FROM cdr
WHERE date
BETWEEN \'2010-08-01\'
AND \'2010-08-31\'

My database include datas from each quarters . As you can see in the QUERY I reduce the result only to august. So wuery result should returns only p2010_q3 partition but instead of this it returns me all subpartitions names...

Do this behavior is normal ?

+----+-------------+-------------------+-------------------------------------+-------+-------------------+--------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------------------------------------+-------+-------------------+--------+---------+------+------+--------------------------+
| 1 | SIMPLE | cdr | p2010_q1,p2010_q2,p2010_q3,p2010_q4 | range | UNIQUE,INDEX_DATE | UNIQUE | 8 | NULL | 4 | Using where; Using index |
+----+-------------+-------------------+-------------------------------------+-------+-------------------+--------+---------+------+------+--------------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
about SUBPARTITIONING BEHAVIOR ...
2607
August 18, 2010 07:46AM


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.