Re: Modify primary key for partitioning
Posted by:
Rick James
Date: August 23, 2012 09:16AM
How big are the records? I see a LONGTEXT and a MEDIUMTEXT; are their values rather big?
SHOW TABLE STATUS
> Most of the selects are based on the "received_date" (BETWEEN ... AND ...)
If nothing else is checked for, that will be a scan of perhaps 360K rows, assuming it needs to scan one full partition. This could be time consuming.
Would you do this, for two reasons:
SELECT COUNT(*), AVG(LENGTH(XMLCERTIFICATE)), AVG(LENGTH(XMLPOSTIT))
FROM My_Table;
Please note how long it takes, and comment on how acceptable that amount of time is.
Also, I wan to see the current number of rows and sizes of those big fields.
Are your received_date BETWEENs typically on the order of 6 months? Or much less? If much less, then more, shorter, partitions may be better.
Based on you answers, I may (or may not) agree with Aftab's suggestion of not partitioning.
A non-partitioned table with PRIMARY KEY(received_date, ...) would not use any other indexes, but might run fast enough.
If your query is just on received date, then this is optimal.
If you do partition, then you get "pruning" to limit the number of partition(s) to look in, then some other index can kick in. And the PK should probably not start with received_date.