List counts
Posted: 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.
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.