MySQL Forums
Forum List  »  Partitioning

Performance doubt
Posted by: Xavier Borrut
Date: March 26, 2013 06:41AM

Hello,

I'm developing an app where we'll have a lot of data and I think data partition will help to make the queries run faster. But we are not sure what type of partition to use.
We want to part our table by a year-month basis, because the queries will always query data within the same month of a given year.

To ease the process, we are going to create an int field named "yearmonth" and create an index and the partition with it. The field values will be the year and month concatenated yyyymm; for example, for the date 18/01/2013 the value will be 201301.

We want to have a partition for every month, and we are not sure what's best.

OPTION 1: Range partition

PARTITION BY RANGE ( yearmonth ) (
PARTITION p0 VALUES LESS THAN (201301),
PARTITION p1 VALUES LESS THAN (201302),
PARTITION p2 VALUES LESS THAN (201303),
...
PARTITION pN VALUES LESS THAN MAXVALUE
);

Pros: We have the exact partition number we need.
Cons: We'll have to add a new partition set every time we approach the last yearmonth.

OPTION 2: Key partition

PARTITION BY KEY( yearmonth )
PARTITIONS 1024;

Pros: No need of any maintenance for the next 85 years.
Cons: Lots of empty partitions.

Note: 1024 it's an limit case, we can use something like 120 to store 10 years of data.


Our doubt is wether to use key or range partition, mainly because of the "unused" partitions. Will this extra partitions slow down the queries of affect any other processes? Just FYI, we know that we will have to use the yearmonth field as the first WHERE condition in order to select the right partition number but again, still not know if the empty partitions can affect us in a wrong way.

Many thanks in advance,

Xavier Borrut

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance doubt
2297
March 26, 2013 06:41AM
1265
March 28, 2013 12:34AM
939
April 09, 2013 01:49AM


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.