List counts

Discussions about development of VICIDIAL and astGUIclient

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

List counts

Postby Acidshock » Mon May 06, 2013 1:35 pm

I notice that when grabbing all the lists and the counts for each database vicidial runs this query:

SELECT vls.list_id,list_name,list_description,count(*) as tally,active,list_lastcalldate,campaign_id,reset_time,DATE_FORMAT(expiration_date,'%Y%m%d') from vicidial_lists vls,vicidial_list vl where vls.list_id=vl.list_id group by list_id order by list_id;

This can produce some rather large query times. Would we be better using these queries and assembling the counts in PHP.

select * from vicidial_lists;
select list_id,count(list_id) from vicidial_list group by list_id;


I have tested this on a vicidial_list table that has 22 million+ leads in it and the query finishes in 1.7 seconds compared to the other query which finishes in 8.6.
VERSION: 2.14-698a | BUILD: 190207-2301 | Asterisk:13.24.1-vici | Vicibox 8.1.2
Acidshock
 
Posts: 428
Joined: Wed Mar 03, 2010 3:19 pm

Re: List counts

Postby omarrodriguezt » Mon May 06, 2013 2:45 pm

Sounds good.
Can you please post a patch in the vicidial issue tracker
http://www.ITContinental.com
Dedicated USA Servers - Vicibox - Vicidial - Limesurvey - Vtiger CRM - More than 15 years experience - Hablamos Español
omarrodriguezt
 
Posts: 667
Joined: Fri Jun 05, 2009 12:22 pm
Location: Dominican Republic

Re: List counts

Postby mflorell » Tue May 07, 2013 5:05 pm

Or you can just turn off list counts in System Settings :)

I would certainly like to look at your patch, please post it to the Issue Tracker.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: List counts

Postby omarrodriguezt » Wed May 08, 2013 10:23 am

Thank you Matt!
omarrodriguezt
 
Posts: 667
Joined: Fri Jun 05, 2009 12:22 pm
Location: Dominican Republic

Re: List counts

Postby bobbymc » Tue May 21, 2013 5:13 am

joins of any kind are the death of mysql unless you go into indexing and doing that between two tables and making is still effective can be a challenge
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: List counts

Postby Acidshock » Wed May 22, 2013 11:34 am

Thats why it crawls so badly on systems with large amounts of leads. I am going to write a patch for it but have been rather busy these last couple weeks.
VERSION: 2.14-698a | BUILD: 190207-2301 | Asterisk:13.24.1-vici | Vicibox 8.1.2
Acidshock
 
Posts: 428
Joined: Wed Mar 03, 2010 3:19 pm


Return to Development

Who is online

Users browsing this forum: No registered users and 29 guests