Re: Partition based on dynamic customer list

[Phil Hildebrand]

I suppose you could 'fake' a hash partition for your customer id, and then use a hash partition for your date range..

Something like:

CREATE TABLE customer_list_values
(
id int NOT NULL AUTO_INCREMENT,
created_date datetime NOT NULL,
value varchar(1000),
PRIMARY KEY (id,created_date)
)
PARTITION BY LIST ( mod (id,3)) -- Fake partition by hash --
SUBPARTITION BY HASH (month (created_date))
SUBPARTITIONS 12
(
PARTITION p0 VALUES IN (0),
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2)
);


mysql> insert into customer_list_values (created_date,value) values ('2008-10-01','one'),(now(),'two'),('2008-08-03','three');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> explain partitions select * from customer_list_values where id = 3 and created_date = '2008-08-03';
+----+-------------+----------------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | customer_list_values | p0_p0sp8 | system | PRIMARY | NULL | NULL | NULL | 1 | |
+----+-------------+----------------------+------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)