MySQL Forums
Forum List  »  Partitioning

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Modify primary key for partitioning
1293
August 23, 2012 09:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.