MySQL - Partitioning vs multiple table suggestion for a use case

Question

We are having around 30,000 customers and each customer is having multiple products. We are currently storing all the products in a single table partitioned by KEY(customerid). I would like to get your suggestions if separate tables for each customer would be more beneficial over the partitioning OR we continue to use partitioning with current (HASH) or different type.

1. Number of products per customers varies, a few customers having > 1M products while some customers having as small as a few hundred products. This may result in not so perfect partitions.
2. If a customer account is to be deleted, so will be all products of that customer. In case of separate tables, this would be quite useful.
3. All customers are disjointed. So there is no query to access cross-customer products.
4. Number of customers are quite large (around 30k), I am not sure if that's a good idea to have so many tables.
5. Is any other partitioning scheme is better than what we currently using.

Thank you for your inputs.

Answer 1

Generally I would go with the single table solution that you already have, it's the simple, straight-forward way to go.

You don't mention your motivation for wanting to change your setup.

How many entries do you have in your products table?

Are you experiencing performance issues with your current setup? If not I might be inclined to call this a case of "premature optimization".

If you ARE experiencing performance issues I would start by analyzing those first (profiling) to determine whether they are caused by your single products table design being a bottleneck.

Practical advice I can offer: Make sure you are using InnoDB storage engine and not MyISAM since that will allow for row level locks.

The downside to your proposal of having one table for each customer is maintenance and complexity. If you ever want to change your schema of the product tables it will be a lot more complicated and error prone task than before. You might have to make a script to batch the changes of all those tables, and what if the script crashes halfway? Then half of you customers have a changed table schema and the other half doesn't. As I mentioned if you do not currently have a performance problem you would be adding this complexity and maintenance without gaining anything.

You state that "All customers are disjointed. So there is no query to access cross-customer products." however it might not stay that way forever. Imagine in 2 months you need to extract a list of all customers who own specific product of type x, that would be a simple SQL query in your current setup, in the multi-table setup you would have to make a script or small program that could iterate over all customers and for each customer make a product query. So what was 1 query before is now 30.000 queries.

What you propose is a simple form of sharding. If you decide to go that way you may want to look into sharding since there are other ways to approach than the somewhat aggressive approach of giving every customer a dedicated table. E.g. use a hash of each customer id as sharding key, so every customer is either part of group A or group B. Products owned by A-customers are in ProductTableA, products owned by B-customers are in ProductTableB. (in a real implementation you may want to hash to a value between 0-255 and then keep a reference list saying that 0-127 are table-A, 128-255 are table-B, that way if you ever decide to scale up and add one more table, you don't have to recalculate all your hashes you just update your reference list).