by rey_philip » Tue Oct 23, 2007 12:12 pm
Hi to all or developers of vicidial,
I need your help on Asterisk database (vicidial). By the way
Im Rey Im an intermediate PHP programmer, mostly I do reports
and statistics based from asterisk database. I also do manage the database in our company.
The first problem is that vicidial_agent_log and vicidial_log were not synchronized when it comes to status, example one agent has 25 sales
on vicidial_agent_log then if I look at vicidial_log his sales is just 24. The good part is that vicidial_agent_log is accurate than vicidial_log, so we have atleast an accurate table to get the reports.
But here's the second problem, yes vicidial_agent_log is accurate but when you query on that table like this
`select user, status from vicidial_agent_log where user = 'user1' and
status = 'sale' and event_time between '2007.....' and '2007....'`
it takes atleast 2 seconds to output the results.
What I did is that, I look at the structure of the vicidial_agent_log table
and saw that there are no index, just a primary key which is agent_log_id which is useless when I execute my query. So what I did is that I back up the database and created my own database in my computer. I did some experimentation on vicidial_agent_log table, I index the user, status and event_time and when I execute the query, it just takes .002 secs, which is very effective, but I haven't implement it in our production yet cause I want to make sure that If I edit the vicidial_agent_log table nothing will happen on the vicidial system.
So guys (developers of vicidial or matt), just want to ask. Is it okey
to add an index user, status and event_time to vicidial_agent_log
and nothing bad will happen to vicidial?
Also, I want to ask, what are the factors why the vicidial_log and vicidial_agent_log are not synchronized? And how can we fix that?
I really need your advice and suggestions.
Thanks,
Rey