MySQL Forums
Forum List  »  Partitioning

Range Partition
Posted by: Sam Mathews
Date: June 17, 2017 06:09AM

Hi!,
I am using mysql 5.6.7.
My Table structure is

CREATE TABLE test1 (
id int(255) NOT NULL AUTO_INCREMENT,,
fname varchar(255) NOT NULL,
lanme varchar(255) NOT NULL,
doj timestamp NOT NULL default '0000-00-00 00:00:00',
hash varchar(255) NOT NULL,
PRIMARY KEY (ID,doj )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are four Indexes.

I have create range partition on daily basis on doj column as my table size 300GB.
Everyday there is a bulk insert of 1 Billion rows in this table.

As of now insertion is very slow. We are using .1million row in one insert query.
It is taking more than 24hrs to insert all rows.

I want to know
1. is partition useful in speeding up insertion of data?
2. Indexes will be managed locally or globally?
3. Is there any recommendation about creating partition like (not to create more than 100 partition per table)?
4. Is there any other way using which we can increase insertion speed?

Options: ReplyQuote


Subject
Views
Written By
Posted
Range Partition
100
June 17, 2017 06:09AM
46
June 17, 2017 10:41AM
37
June 18, 2017 01:05AM
42
June 18, 2017 10:52AM


Sorry, only registered users may post in this forum.

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.