MySQL - Indexing Partitioned Column
1024 partitions means 1024 "sub-tables".  Certain operations will open all 1024.
A table (or partition) is implemented using files in the OS.  You are at the mercy of the OS speed or sluggishness.
If you have WHERE ClientId = 123 that should lead to "partition pruning", which leads to opening the one partition, then scanning the entire partition.
INDEX(ClientId), without partitioning, would be almost as good.


This would be better:

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ClientId, id),
INDEX(id)


Now the data is clustered on ClientId, there is no extra thing to "open", and the performance will be better.

Please provide the main queries you have so that I can check whether I have messed up other cases.

Other issues...

AccountId BIGINT,
AccountNumber VARCHAR(250),


sounds grossly redundant.  Is there a 1:1 mapping between that Id and that Number?  Then the pairs should be in another table, and only the Id should be in this table.  Ditto for Devices.

Making that change would significantly shrink this table, thereby making operations somewhat faster.

Meanwhile, learn about JOIN in order to get the ...Number from the ...Id.

More on partitioning.