1

I have two tables: userMessages and userStatistics

I realized that I need to set up a partitioning in order to ensure efficiency. With all the information I could gather, I am suppose to use HASH partitioning. 

PARTITION BY HASH(user_id) PARTITIONS 101

Why do I have to define number of partitions? Is it possible to partition by number of users? I want to partition all the messages and statistics by each user. What partitions number should I use?

More Context

Let use my userStatistics for example. This will store a new entry every day to capture daily activity of users impressions and click-throughs etc... I expect this database to get very large over time. I expect it to be very large within a year (>1m rows). I was thinking of just creating separate tables for each user using an index, but was told about partitioning using HASH. What is the best way to approach this case?

Maciek Semik
  • 1,872
  • 23
  • 43
  • You have to define Partition number for `HASH` partitioning as it is equally distributes data in partitions. If you want to separate data by UserID try using `Range Partition` – Abhishek Ginani Jan 14 '16 at 05:34

1 Answers1

0

Partition by HASH will not provide any efficiency. In fact, there are only a few RANGE partitionings that provide any efficiency. I suspect your use case does not apply. See http://mysql.rjweb.org/doc.php/partitionmaint

Describe your use case further if you would like to debate the issue.

Rick James
  • 135,179
  • 13
  • 127
  • 222