Re: Partitioning or indexing for millions of records

[Aamir Sohail]

hii,

> Please SHOW CREATE TABLE (at least as it currently stands).

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

>For MyISAM, set key_buffer_size to about 20% of _available_ RAM.
how to set this and is there any side effects of this ?

schema is normalized

>select statements are like:
select c.msisdn,sum(c.dataVolumeDownLink+c.dataVolumeUpLink) as datasum
from cdr c
where c.eventDate>=<date_var>
group by c.msisdn
order by datasum desc; -- this query also searching whole 20M records after indexing.

I think this is all you require.
So please tell me how to overcome with this problem.

And thank you sir for your time. :)