Hi,
I was wondering why the MySQL cdr table definition does not contain a primary key (SQL/cdr_mysql_table.sql).
Also, I am interested to know how others perform maintenance of this table. What do most of you do when the table gets really big (supposing you want to preserve old data somewhere but keep the active MySQL database “light”)? Likewise for the /var/log/ csv files.
Thanks for your suggestions.
Vieri
I’m not sure why it doesn’t have a primary key by default but I suppose you could make 1 across ‘src’, ‘dst’, ‘calldate’ and ‘billsec’. This should produce a unique key and since those fields are fairly commonly searched on in reports, the reporting performance should improve as well. The only caveat is that the inserts will be slightly slower because it needs to update the key with each insert but that time used to update the key should be negligible.
you can index it with-out doing a primary key. It’s not clear you want a primary key - I wonder how Asterisk would react if there was a primary key and somehow there was a duplicate? I wouldn’t want to find out - it does not always handle error conditions that it wouldn’t expect great.
That is a good point. Indexing without forcing the uniqueness a primary enforces would be safer. You wouldn’t want to lose call records and piss off Asterisk if a duplicate did occur.
Dean