MySQL Forums
Forum List  »  Partitioning

Re: Splitting large table by two fileds
Posted by: Mikael Ronström
Date: September 12, 2007 12:00PM

Hi,
Your partition function is to complex to allow for
partition pruning. If you partition on year only
e.g. you'll see better effect. If you use a date
column you can also use the to_days function as
discussed in the manual.

Rgrds Mikael


Evgeniy Bulichev Wrote:
-------------------------------------------------------
> Hi.
> I have the following table:
> CREATE TABLE base (
> period tinyint(4) NOT NULL DEFAULT '0',
> year tinyint(4) NOT NULL DEFAULT '0',
> value double NOT NULL DEFAULT '0',
> key yp (year, period),
> key p (period)
> );
> This table contains more than 50 Gb of statistical
> data, and grow in size every week.
> There is a task to transfer this table
> periodically (2 times a month) to several servers.
> It is necessary to copy it to large data medium
> (in our case USB-drive),
> so a lot of time for copying to/from this medium
> is spent.
> With introduction of partitions to MySQL I had an
> idea to split my table on 'years' and 'periods'
> and to transfer only updated (last) partitions.
> For this purpose the following junction table
> containing relations between years and periods has
> been created:
>
> CREATE TABLE corr_year_period (
> year tinyint(4) NOT NULL DEFAULT '0',
> period tinyint(4) NOT NULL DEFAULT '0',
> KEY yp (year,period),
> KEY p (period)
> );
> insert into corr_year_period (year, period) select
> distinct year, period from base;
>
> And table 'base' was re-created this way:
>
> DROP TABLE base;
> CREATE TABLE `base` (
> period tinyint(4) NOT NULL DEFAULT '0',
> year tinyint(4) NOT NULL DEFAULT '0',
> value double NOT NULL DEFAULT '0',
> key yp (year, period),
> key p (period)
>
> )
> partition by list(year*100+period)(
>
> partition y95p2 values in (9502),
> partition y95p3 values in (9503),
> partition y95p4 values in (9504),
> partition y95p5 values in (9505),
> partition y95p6 values in (9506),
> partition y95p7 values in (9507),
> partition y95p8 values in (9508),
> partition y95p9 values in (9509),
> ....
> partition y107p1 values in (10701),
> partition y107p2 values in (10702),
> partition y107p3 values in (10703),
> partition y107p4 values in (10704),
> partition y107p5 values in (10705),
> partition y107p13 values in (10713),
> partition y107p20 values in (10720)
> );
>
> Here partitions go through all possible
> combinations of 'year' and 'period' by means of
> this query:
> select concat('partition p',year,'p',period,'
> values in (',year*100+period),'),') from
> corr_year_period;
>
> So, my questions:
> 1. Why MySQL uses ALL partitions if I query it
> with
> 'select * from base where year=106'
> (that is, if I use in my query only 'year' without
> 'period').
>
> 2. If I fix field 'period' in this query this way:
>
> 'select * from base b, corr_year_period cyp where
> b.year=106 and cyp.year=b.year and
> b.period=cyp.period'
> MySQL again uses all partitions! But if I specify
> all my periods this way:
> 'select * from base where year=106 and period in
> (1,2,3,4,...25)'
> MySQL will use only 25 partitions (y106p1, y106p2,
> ... y106p25). Why?
>
> 3. I have a scientific guess that if I keep using
> partitions thus, I can drop my keys 'yp' and 'p'
> (judging by time of execution of query it is so).
> I want to ask - is this REALLY so?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Splitting large table by two fileds
2681
September 12, 2007 12:00PM


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.