MySQL Forums
Forum List  »  Partitioning

Re: When partition a table
Posted by: Rick James
Date: March 31, 2012 06:53AM

* Don't bother PARTITIONing unless you expect least a million rows. You are unlikely to get any performance improvements for smaller tables.

* One of 3 use cases. All of these use "PARTITION BY RANGE".

** "Rolling data" -- Example: You have info you want to purge after 90 days. Create 90 partitions (plus a couple), and have a daily script to add a new partition (REORGANIZE PARTITION) and drop old data (DROP PARTITION).

** You need two "clustered" indexes. PARTITION does not really give you that, but it can help enough to make it win.

** You have (1) most activity in the 'latest' partition, and (2) you have secondary indexes that are very random (GUID, MD5, etc).

I have not found use cases for
* non-"RANGE" partitioning
* SUBPARTITIONing
* non-performance reasons.

Does anyone else have some use cases?

Options: ReplyQuote


Subject
Views
Written By
Posted
2453
March 28, 2012 11:15AM
Re: When partition a table
1604
March 31, 2012 06:53AM


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.