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