MySQL Forums
Forum List  »  Partitioning

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition by KEY - pruning?
3384
November 28, 2007 05:17PM
2373
December 08, 2007 01:36AM


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.