Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes?
Posted by: Aamir Sohail
Date: December 02, 2013 12:49AM

Thank you sir, this was very helpful.
Yesterday I made some test on this table.

>KEY `msisdn_index` (`msisdn`)
I have already removed this one.

First I inserted 80 million records using by disabling keys command-

INSERT INTO test_cdr (id,dataPacketDownLink, dataPacketUpLink,dataPlanEndTime,dataPlanStartTime,dataVolumeDownLink,dataVolumeUpLink,dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, eventDate, msisdn, QUARTER, validDays, dataLeft,completedOn,evedate)
SELECT id,dataPacketDownLink,dataPacketUpLink, dataPlanEndTime, dataPlanStartTime, dataVolumeDownLink, dataVolumeUpLink, dataplan,dataplanType, createdOn, deviceName, duration, effectiveDuration, HOUR, eventDate, msisdn, QUARTER, validDays, dataLeft,completedOn,DATE(eventdate)
FROM cdr;

which took 14 min 37.93 sec.

And then i enabled keys and again inserted 20 million records using the same query but another temporary table with 20m records and it took-

9 min 56.7 sec.

So I think 30,000 in 15 minutes not gonna hurt its performance and table lock also not gonna happen

> I suspect you will find not query using hour_index.

Actually I have queries using this index in group by clause thats why i chose this. Is it correct move or wrong? because I can see in explain only query with hour in "where" is using this index.

Options: ReplyQuote


Subject
Written By
Posted
Re: is it good add 3 index on a table with 30,000 insert in every 15 minutes?
December 02, 2013 12:49AM


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.