MySQL Forums
Forum List  »  Partitioning

Re: Partition and pruning by ENUM
Posted by: Jonathan Stephens
Date: November 06, 2006 03:34AM

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
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
6152
October 30, 2006 05:36AM
Re: Partition and pruning by ENUM
3251
November 06, 2006 03:34AM


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.