MySQL Forums
Forum List  »  Partitioning

Splitting large table by two fileds
Posted by: Evgeniy Bulichev
Date: July 02, 2007 08:07AM

I have the following table:
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:

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

Written By
Splitting large table by two fileds
July 02, 2007 08:07AM

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.