Is it possible to make more than 1024 partitions in mysql table?

Question

I want to make two tables. One is user table and another one is users data table.And I want to link them by using the "id" like below

table A (user)

1 | user 1

2 | user 2

table B   (userdata)

1 | data 1

1 | data 2

1 | data 3

2 | data 1

2 | data 2

2 | data 3

I want to partition userdata (table B) for each user in table A. If I have 10 users (10 rows in user table (table A)) then I do 10 partitions for each user in userdata (table B) table.

My question is if I have more than 100000 users then is it possible to partition userdata (table B) table into 100000 partitions so that each partition gets one user in table A?

According to mysql 5.5 limitations we cannot partition table more than 1024. So is there any alternative to store userdata so that I can easily search data of each user?

create [Junction Tables](http://stackoverflow.com/a/32620163), normalized data, and disavow partitioning (as I see it), until you need such for massive amounts of data. I think what you have stuck in your head is "keep them separate"="partitioning" ... unrelated — Drew, Oct 26 '15 at 14:03
In your case it is not such much Junction tables, as it is, hey, just store my data with a Foreign Key — Drew, Oct 26 '15 at 14:04

score 2 · Answer 1 · answered Oct 26 '15 at 14:06

The point of partitioning is not to create separate tables per key, but to be able to create smaller, more managable chunks of data. Imagine, if you use 1024 partitions and each of those partitions have 1024 customers, then you have more than 1 million customers already. How long do you think it would take mysql to search a couple thousands of records in each partition to get you the data for a single customer? Not too long.

Moreover, if you split a table by each and every user, than managing those partitions (e.g. identifying in which partition a particular user id is located) would take at least as much time as finding the records of a particular user within an unpartitioned table.

So, you are simply over worrying the situation.

Thank you for your reply. I really worry about the performance if I just make userdata (table B) without any partitions.Is there any alternative way to search for userdata quick ? will Foreign Key usage can handle ? Thank you in advance — , Oct 26 '15 at 14:18
You can use partitioning as a mean to speed up queries to a table. However, you should not worry if 1024 partitions were enough. — Shadow, Oct 26 '15 at 14:25

score 0 · Answer 2 · answered Oct 26 '15 at 14:04

This is too long for a comment.

Your question is irrelevant. Why would you want to be partitioning on individual users? I can think of no logical reason for this, particularly for the small amounts of data that you are working with.

Sometimes, it is useful to partition by ranges of user ids (where the ranges might be based on a hash function). More often, the partitioning key is related to some aspect of time.

In your case, a simple index should be sufficient. And, much, much less wasteful. If you implemented your exaple, the space needed for the data is probably 10-100 larger than it needs to be, because of unfilled data pages in the individual partitions.

Is it possible to make more than 1024 partitions in mysql table?

2 Answers2