Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am posting the same question as I posted in SO, but somebody advice me to post it here.

I have a 16GB big table with 8 partition. And RAM 4 GB.

I am running a query -

SELECT c.eveDate,SUM(c.dataVolumeDownLink)+SUM(c.dataVolumeUpLink) 
FROM cdr c 
WHERE c.evedate>='2013-10-01'
GROUP BY evedate;

Which is using temporary file and taking 5 minutes(because table have 140 million records).

So How much I should adjust my tmp_table_size so this query dont use any temporary table. I have already set some parameters like -

tmp_table_size =2G
key_buffer_size =1G

EDIT: Details Required-

Show create table :

CREATE TABLE `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 NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`,`evedate`),
  KEY `evedate_index` (`evedate`),
  KEY `eve_hour_index` (`evedate`,`hour`),
  KEY `eve_msisdn_index` (`evedate`,`msisdn`)
) ENGINE=MyISAM AUTO_INCREMENT=259341694 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(evedate)
(PARTITION `START` VALUES LESS THAN ('2013-09-01') ENGINE = MyISAM,
 PARTITION p01 VALUES LESS THAN ('2013-09-08') ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN ('2013-09-15') ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN ('2013-09-22') ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN ('2013-09-29') ENGINE = MyISAM,
 PARTITION p05 VALUES LESS THAN ('2013-10-06') ENGINE = MyISAM,
 PARTITION p06 VALUES LESS THAN ('2013-10-12') ENGINE = MyISAM,
 PARTITION p07 VALUES LESS THAN ('2013-10-19') ENGINE = MyISAM,
 PARTITION p08 VALUES LESS THAN ('2013-10-25') ENGINE = MyISAM,
 PARTITION p09 VALUES LESS THAN ('2013-10-31') ENGINE = MyISAM,
 PARTITION p10 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */

Explain Plan for the query:

id 1 
select_type  SIMPLE
table  C
type  ALL
possible_keys  evedate_index,eve_hour_index,eve_msisdn_index                                
key  null
key_len  null
ref  null
rows   79200000     
Extra     Using where; Using temporary; Using filesort                                       

Thank you.

share|improve this question
    
You should also post statement outputs off these queries show create table [table_name] and explain [your_sql] .. –  Raymond Nijland Dec 9 '13 at 20:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.