Partition based on dynamic customer list

[Todd Williams]

Hello,
I need some partitioning advice. I’ve searched the forums, documentation, and books and cannot find a good way to do what I want, but that’s probably because I am a noob. That said, here is my quandary: I have a lot of application event data related to multiple customers and I’d like to partition that data based on customer and subpartition based on date range. I want to partition based on customer for efficiency of queries, since multi-customer queries are not needed, and to improve data isolation.

This would seem easy, combining range partitioning with list partitioning but I need to support a large and dynamic list of customers. Since I cannot statically or pre-define the customer list, and that list could reach the thousands, I cannot find a good way to do this. Since the customerID is an incrimenting INT, I could subpartition by customer by grouping customers ("PARTITION p0 VALUES LESS THAN (500)") but that seems sloppy and probably wouldnt help much.

Should it matter, each record is standalone from all other records, and doesn’t change, so I am planning on using Archive engine. I will use MySQL 5.1