MySQL Forums
Forum List  »  Partitioning

Re: Partitioning or indexing for millions of records
Posted by: Aamir Sohail
Date: November 28, 2013 12:40AM

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. :)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning or indexing for millions of records
1477
November 28, 2013 12:40AM


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.