MySQL Range partitioning vs Hash partitioning We have one large table with 100 million records and its size is 100 GB and data is continuously growing day by day. This table has 30 partitions with RANGE by client_id(table's column). One client_id is having minimum 1 million records. 

For solution we have break this large table into 30 small tables with group of client_id data in each table. Now we want to do partition in each table.


So which partition would work for us best: Range or Hash?


We don't know which table has which set of client_id.

Thanks
Short answer: Neither.

Long answer:

Partitioning is not a panacea.  It does not automatically provide performance.  In fact, it sometimes slows down processing.

I have never see a use case where BY HASH helped.  I have seen a few use cases for BY RANGE.  Off hand, it does not sound like your use case will benefit.

Please provide SHOW CREATE TABLE and a few of the critical SELECTs.  With those, I will either


Explain why partitioning is useless.  Or...
Explain how to benefit from partitioning for those queries.


In either case, I will probably suggest index (including PK) changes.  Proper indexes is where performance usually comes from.

In another direction...  Is this some form of "multi-tenant" app?  Will it eventually out-grow the server?  If so, Sharding may be in your future.  This is when you scatter "clients" (or whatever) across multiple servers.  But, alas, MySQL does not have any built-in support of such.  There are 3rd party packages (eg, Spider) that do.