MySQL Manual for 5.1 says: "This limitation on pruning with composite partitioning keys is removed in MySQL 5.5."
here:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
I disagree with this statement. It's still there in 5.5:
mysql> show variables like 'version';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| version | 5.5.25a-log |
+---------------+-------------+
1 row in set (0.00 sec)
mysql> show create table node_cksum;
| node_cksum | CREATE TABLE `node_cksum` (
`nodeid` int(11) NOT NULL,
`tablename` varchar(64) NOT NULL DEFAULT '',
`recordid` bigint(20) unsigned NOT NULL,
`cksumtype` int(11) NOT NULL DEFAULT '0',
`cksum` text NOT NULL,
`sync` char(128) NOT NULL DEFAULT '',
PRIMARY KEY (`nodeid`,`cksumtype`,`tablename`,`recordid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY ()
PARTITIONS 12 */ |
-----------------------------------+
1 row in set (0.01 sec)
mysql> explain partitions select * from node_cksum where nodeid = '4' and cksumtype = '1';
+----+-------------+------------+---------------------------------------+------+---------------+---------+---------+-------------+---------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+---------------------------------------+------+---------------+---------+---------+-------------+---------+-------+
| 1 | SIMPLE | node_cksum | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11 | ref | PRIMARY | PRIMARY | 8 | const,const | 1970579 | |
+----+-------------+------------+---------------------------------------+------+---------------+---------+---------+-------------+---------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from node_cksum where nodeid = '4' and cksumtype = '1' and recordid = '400400000278377';
+----+-------------+------------+---------------------------------------+------+---------------+---------+---------+-------------+---------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+---------------------------------------+------+---------------+---------+---------+-------------+---------+-------------+
| 1 | SIMPLE | node_cksum | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11 | ref | PRIMARY | PRIMARY | 8 | const,const | 1995373 | Using where |
+----+-------------+------------+---------------------------------------+------+---------------+---------+---------+-------------+---------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from node_cksum where nodeid = '4' and cksumtype = '1' and tablename = 'items' and recordid = '400400000278377';
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------------------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------------------+------+-------+
| 1 | SIMPLE | node_cksum | p2 | const | PRIMARY | PRIMARY | 210 | const,const,const,const | 1 | |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------------------------+------+-------+
1 row in set (0.00 sec)