Partition by KEY - pruning?
Posted by:
bob grand
Date: November 28, 2007 05:17PM
12 partitions created using "Partition by KEY". Key made up of all non-INT columns.
EXPLAIN ouput below implies pruning is taking place as only partition p6 is being checked, I think.
The documentation seems to suggest that pruning only occurs if columns used in key are evaluated to INT.
Am I missing something?
server version is 5.1.22.
ENGINE is InnoDb
CREATE TABLE `t1` (
`host` varchar(255) default NULL,
`TextID` varchar(255) NOT NULL default '',
`altID` varchar(64) default NULL,
`nCount` int(11) NOT NULL default '1',
`Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `TextID` (`TextID`,`host`,`altID`),
KEY `altID` (`altID`),
KEY `Timestamp` (`Timestamp`,`altID`,`host`)
) PARTITION BY KEY( TextID, host, altID)
PARTITIONS 12;
After loading up the partitions...
mysql> explain partitions select * from t1 where TextID='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' and host ='' and altID=''\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: p6
type: const
possible_keys: TextID,altID
key: TextID
key_len: 391
ref: const,const,const
rows: 1
Extra:
1 row in set (0.00 sec)
Note: partition output shows only the partition where the unique row is found.