[Asterisk-Users] MY Sql CDR

[Tilghman Lesher]

On Sunday 21 September 2003 11:30 pm, Uriel Carrasquilla wrote:
> I like it.
> I am thinking of putting this query in a C++ but I am a bit concern
> on 1) scalability
> 2) delays in setting up the calls
> shoud I be concerned?

The query:
mysql> select sum(billsec) from cdr where calldate >
'2003-09-01 00:00:00' and '2' in (src,dst);

This gets into the meat of database management, so it's a little far
afield from this list, but I'll try to explain as best I can.  It
really depends upon the design of the database table and the indexes
you have on various fields.  For my referenced query, you'll want to
have indexes on the fields in the WHERE clause (i.e. src, dst,
calldate) if you get beyond about 10,000 records or so.

Also, make sure that you define your table with type CHAR, not type
VARCHAR, and have no text or blob fields.  This will ensure that the
table will be fixed width, which can speed up database access.  And,
you'll want to have an archival policy, whereby old records are purged
from the active table.  This keeps your table and index sizes down.

If the database resides on the same machine as the Asterisk
installation, use the Unix socket method of connecting to the
database, as this removes the overhead of TCP and could produce
as much as a 15% overall speed improvement.

-Tilghman