CAST is no longer allowed in partitioning expressions beginning with MySQL 5.1.12. (See
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html#partitioning-limitations-disallowed-functions for details.)
Solution: Store en1 as an INT. Use another table to store the names. This is much better normalisation than using ENUM, in any case.
Using HASH partitioning like this is not a really very good idea IMO, as it tends to be confusing. However, pruning does take place when en1 is defined as INT:
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | temp | p1 | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------------+
However, I think things will be clearer if you use LIST partitioning, like so:
CREATE TABLE temp (
en1 INT NOT NULL,
date DATE,
PRIMARY KEY(en1,date)
) ENGINE=InnoDB
PARTITION BY LIST(en1)(
PARTITION p0 VALUES IN(1),
PARTITION p1 VALUES IN(2),
PARTITION p2 VALUES IN(3),
PARTITION p3 VALUES IN(4)
);
mysql> EXPLAIN PARTITIONS select * from temp where en1 = 1;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | temp | p0 | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.01 sec)
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle