[asterisk-users] Rotating CDR records inside mysql - anyone does it?

[Darryl Dunkin]

You may speed up your queries with proper indexing. The default indexes are included with the table creation script here:
http://www.voip-info.org/wiki-Asterisk+cdr+mysql 

ALTER TABLE `cdr` ADD INDEX ( `calldate` ); 
ALTER TABLE `cdr` ADD INDEX ( `dst` ); 
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );

You could look at running a select/insert query to dump older CDRs off to an archive table (compressed, supports inserts and selects only):
http://dev.mysql.com/tech-resources/articles/storage-engine.html

After that's good, delete the older entires.

-----Original Message-----
From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of tloginbr-asterisk at yahoo.com.br
Sent: Tuesday, January 22, 2008 11:44
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: [asterisk-users] Rotating CDR records inside mysql - anyone does it?

Hi everyone,

I have a few asterisk machines doing PSTN calls, and I keep track of all cdr in a single machine running mysql 5. Since I have a very large amount of records in there, its getting pretty slow to query the database, so I'm wondering if anyone does some type of log rotating, like save the data for a single month inside a separate table and do that every month, so I keep the tables small enough to build my reports. I know this is mainly a mysql question, but maybe someone here has some stored procedures that do this already...

Thanks for all help,

Thiago


      Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/

_______________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users