CODEWITHSUNDEEP
mysqlselectquery-optimizationquery-performancesql-execution-plan
Aamir
Aamir

Reputation: 758

how to write optimize query from given query?

I have a table whose structure is -

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` int(11) DEFAULT NULL,  
                `eventDate` datetime DEFAULT NULL,  
                `msisdn` bigint(20) DEFAULT NULL,  
                `quarter` int(11) DEFAULT NULL,  
                `validDays` int(11) DEFAULT NULL,  
                `dataLeft` bigint(20) DEFAULT NULL,  
                `completedOn` datetime DEFAULT NULL,   
            PRIMARY KEY (`id`),   
            KEY `msisdn_index` (`msisdn`),   
            KEY `eventdate_index` (`eventDate`)   
        ) ENGINE=MyISAM AUTO_INCREMENT=55925171 DEFAULT CHARSET=latin1

Now when I am trying to write this query its taking more than 1 minutes from 20 million records - 

select c.msisdn,sum(c.dataVolumeDownLink+c.dataVolumeUpLink) as datasum from cdr c where c.eventDate>=<date> group by c.msisdn order by datasum desc;

In actuall I have 40+ millions of record.

Explain plan-

id  select_type  table  type  possible_keys    key  key_len  ref  rows      Extra                                         
1   SIMPLE       c      ALL   eventdate_index                     20000420  Using where; Using temporary; Using filesort

I can't make partition, so tell how i can optimize this query. Thank you.

Upvotes: 0

Views: 67

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

Optimization is a bit of an art.

Start by simply putting an index on your eventDate. That will probably get you very close, except for when you are trying to look for a very wide date range where you are trying to get almost all of the records at one time.

Other possibilities might include creating a combined index on msisdn and eventDate. Order in the index does matter, so indexing on msisdn then eventDate is different from indexing on eventDate then msisdn.

Then keep using the analyzer to see what is working and what isn't.

Upvotes: 1

Related Questions