We are working on a project which if successful may have Billion of rows in a single table. Currently, we are working on to build a MVP and will do proof of value.

We are bit worried on the number of potential rows a single table may have. We were looking for guidance on partitioning vs index and/or both. We will have an variable (say customer ID in product table) which can be used for creating almost separate tables for each customer.

Would you suggest that we create a reference table which direct to a product table (which gets created) for a customer? Or we do partitioning only..

Indexes are also designed for cutting down on how much you need to read.

exactly!

It all depends on your workload. I recommend partitioning in a lot of cases. There are many people out there with 2B row tables and only access the last 5% of it 99% of the time.

(I can’t imagine how your example of Hash “load balancing” provides any performance benefit. Do you have ‘proof’?)

Mayank, the solution is simple: Also have INDEX(id). The only thing that AUTO_INCREMENT requires is that the id be first in some index. It does not require PK or UNIQUE. (However, without a uniqueness constraint it is possible to insert a duplicate id — but who would do that!)

I have used this technique several times. It provides the performance benefit of “clustering” on part_id, while still having a UNIQUE primary key. That is, it is beneficial for a SELECT … WHERE part_id = 123 with multiple rows of 123. And for SELECT … WHERE part_id BETWEEN … .

Note that PARTITIONing add no benefit to either of those SELECTs.