List counts

[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.

[omarrodriguezt]

Sounds good.
Can you please post a patch in the vicidial issue tracker

[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.

[omarrodriguezt]

Thank you Matt!

[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

[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.