Re: Modify primary key for partitioning

[Aftab Khan]

How much RAM do you have on the server?
Can we see MySQL config file i.e. /etc/my.cnf

You are using 'COMPRESSED' row format with the table, if the compression works well and it would help reduce table size significantly, then table's data would be more cache-able (but it depends how much RAM do you have ?). And the numbers you have provided i.e. current table size 300k and each month it gets 60k rows. What's the table size now i.e. SHOW TABLE STATUS (As James asked for)

Partitioning can be useful:

1.When the table is too big to fit in memory or when you have lots of historic data that is frequently accessed.

2. It is easier to discard old partitioned than non-partitioned data but in your case this isn't the case.

3. You can use partitioning to avoid some bottlenecks (e.g. pre-index mutexes with Innodb) in specific workloads but this may not be the case for you.

How do you load data into the table? single insert or batch insert e.g. load data infile or insert into values (...),(...)...