blog post

Requirement: Partition is reliable way for improving performance of DB.It is very useful if Read and write depend on per day mean date based operation. like weather data,forecasting data ,power data .
create table `sport` (
`sport_uid` int(11) not null auto_increment,
`country_uid` int(11) default null,
`stadium_uid` int(11) default null,
`for_date` datetime default null,
`modified_date` datetime default null,
`sport_type` int(3) default null,
primary key (`sport_uid`),
key `fk_country_idx` (`country_uid`),
key `fk_stadium_idx` (`stadium_uid`),
constraint `fk_country_idx` foreign key (`country_uid`) references `country`
(`country_uid`) on delete no action on update no action,
constraint `fk_stadium` foreign key (`stadium_uid`) references `stadium`
(`stadium_uid`),
) engine=innodb default charset=utf8 comment='' '''

................................................... alter table `sport` drop primary key ,add primary key (sport_uid,for_date)
alter table `sport` drop foreign key `fk_country_idx`
alter table `sport` drop foreign key `fk_stadium_idx`

................................................................................
change table definition
'create table `sport` (
`sport_uid` int(11) not null auto_increment,
country_uid` int(11) default null,
`stadium_uid` int(11) default null,
`for_date` datetime not null default ''0000-00-00 00:00:00'',
`modified_date` datetime default null,
`sport_type` int(3) default null,
primary key (`sport_uid`,`for_date`),
key `fk_country_idx` (`country_uid`),
key `fk_stadium_idx` (`stadium_uid`),
) engine=innodb default charset=utf8 comment='' '''
alter table `sport` partition by range(to_days(for_date))
(
partition pt0 values less than (736268),
partition pt1 values less than (736275),

..... partition pt119 values less than (737070),
partition ptf values less than maxvalue
)

Table Partition
  • Share This Story


Pleas give your valuable comments to improve the contents.

about author

MONU KUMAR VERMA


I'm Monu Kumar Verma, working as a Mysql DBA with over 5.9 years of experience.Database Migration,Replication,Optimization,Backup Recovery ,Tunning,Upgradation,Partitioning is part of DBA profile.
Visit Profile

Comments (1)

  • Atul Kumar Shukla

    Wednesday, 20 July, 2016 08:54 AM

    Thank you so much @monu for sharing post

Leave a comment

Name
E-mail
Url
Message

Get More Stuff
Subscribing to the TheOneblogs newsletter and get intersting stuff and updates to your email inbox.
Your Email

 

Style Switcher

Change Color

×! We use cookies to improve your user experience. More information