MySQL Forums :: Database Design & Data Modelling :: is it good add 3 index on a table with 30,000 insert in every 15 minutes?


Advanced Search

is it good add 3 index on a table with 30,000 insert in every 15 minutes?
Posted by: Aamir Sohail ()
Date: November 30, 2013 04:46AM

I have to make a table with 6-7 partition like this -


CREATE TABLE test_cdr
( `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`dataPacketDownLink` BIGINT(20) DEFAULT NULL,
`dataPacketUpLink` BIGINT(20) DEFAULT NULL,
`dataPlanEndTime` DATETIME DEFAULT NULL,
`dataPlanStartTime` DATETIME DEFAULT NULL,
`dataVolumeDownLink` BIGINT(20) DEFAULT NULL,
`dataVolumeUpLink` BIGINT(20) DEFAULT NULL,
`dataplan` VARCHAR(255) DEFAULT NULL,
`dataplanType` VARCHAR(255) DEFAULT NULL,
`createdOn` DATETIME DEFAULT NULL,
`deviceName` VARCHAR(500) DEFAULT NULL,
`duration` INT(11) NOT NULL,
`effectiveDuration` INT(11) NOT NULL,
`hour` TINYINT(4) DEFAULT NULL,
`eventDate` DATETIME DEFAULT NULL,
`msisdn` BIGINT(20) DEFAULT NULL,
`quarter` TINYINT(4) DEFAULT NULL,
`validDays` INT(11) DEFAULT NULL,
`dataLeft` BIGINT(20) DEFAULT NULL,
`completedOn` DATETIME DEFAULT NULL,
`evedate` DATE DEFAULT NULL,
PRIMARY KEY (`id`,`evedate`),
KEY `evedate_index` (`evedate`),
KEY `hour_index` (`hour`),
KEY `msisdn_index` (`msisdn`) )
ENGINE=MYISAM
PARTITION BY RANGE COLUMNS(evedate)(
PARTITION p01 VALUES LESS THAN '2013-9-07'),
PARTITION p02 VALUES LESS THAN ('2013-09-10'),
PARTITION p03 VALUES LESS THAN '2013-09-18'),
PARTITION p04 VALUES LESS THAN ('2013-09-23'),
PARTITION p05 VALUES LESS HAN ('2013-09-25'),
PARTITION p06 VALUES LESS THAN ('2013-09-27'),
PARTITION p07 VALUES ESS THAN ('2013-10-01'),
PARTITION p08 VALUES LESS THAN (MAXVALUE));

Is it good to make this indexes on this table(with 40+ millions row), because we are going to insert 30,000 records in every 15 minutes. But I also dont want to hurt insert operations? Indexes are too required for fast queries.

So please tell me some intermediate solution for this.

Thank you.

Options: ReplyQuote


Subject Written By Posted
is it good add 3 index on a table with 30,000 insert in every 15 minutes? Aamir Sohail 11/30/2013 04:46AM
Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes? Rick James 11/30/2013 02:48PM
Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes? Aamir Sohail 12/02/2013 12:49AM
Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes? Rick James 12/02/2013 09:30PM
Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes? Aamir Sohail 12/03/2013 02:17AM


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.