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




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.