Page 1 of 1

List counts

PostPosted: Mon May 06, 2013 1:35 pm
by Acidshock
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.

Re: List counts

PostPosted: Mon May 06, 2013 2:45 pm
by omarrodriguezt
Sounds good.
Can you please post a patch in the vicidial issue tracker

Re: List counts

PostPosted: Tue May 07, 2013 5:05 pm
by mflorell
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.

Re: List counts

PostPosted: Wed May 08, 2013 10:23 am
by omarrodriguezt
Thank you Matt!

Re: List counts

PostPosted: Tue May 21, 2013 5:13 am
by bobbymc
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

Re: List counts

PostPosted: Wed May 22, 2013 11:34 am
by Acidshock
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.