Re: Partition based on dynamic customer list

[Phil Hildebrand]

I suppose it depends on what your goal is. If it's to automatically create new partitions for every new customer, you would have to do that through some kind of script that checks for new customers and splits/adds partitions. If the goal is just to separate the data out physically by customer id and event date, it should work fine as explained. The example is not really a random physical location. Since the list partition is using mod, you can calculate exactly which partition any given id would end up in.

Because there is a limit of 1024 partitions per table, you couldn't store more than that many customer's data in separate partitions (without using multiple tables). And if you were subpartitioning by range, I think you'd be limited to 1024 between both the partitions and sub partitions.

This would, however, allow you to have, say, 100 partitions (thus mod (id,100)), and split out sub partitions by year (say 10). Then you would know that partition p1 would contain the data for customer 1, 101, 201, 301, etc.

The MySQL optimizer would be able to prune these queries, so any query that used customer id 101, should only look for rows that exist in partition 1.

With a HASH subpartition, MySQL also uses mod as the function for splitting data, so if the subpartition was a HASH (YEAR(event_date)) with 10 partitions, then a query for a given year you could also determine the sub partition it would be in (2008 would end up in partition sp8).

If you note the explain plan, data is only selected from partition 1, subpartition 8 when selecting customer 1, for a date in the year 2008:

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

mysql> explain partitions select * from list_test where id = 1 and created_date = '2007-01-01';
+----+-------------+-----------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | list_test | p1_p1sp7 | system | PRIMARY | NULL | NULL | NULL | 1 | |
+----+-------------+-----------+------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

And in the case that the optimizer does not to well on the subpartitions (created_date > x), it would still only scan the subpartitions of the partition that contains that customer id:

mysql> explain partitions select * from list_test where id = 1 and created_date > '2009-01-01' ; +----+-------------+-----------+-------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | list_test | p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3,p1_p1sp4,p1_p1sp5,p1_p1sp6,p1_p1sp7,p1_p1sp8,p1_p1sp9 | range | PRIMARY | PRIMARY | 8 | NULL | 10 | Using where |
+----+-------------+-----------+-------------------------------------------------------------------------------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Hopefully that is a little clearer. Maybe I'm not quite understanding your dilemma ?