Hi I need some help optimizing this code, currently it takes 38 seconds to run the SQL query, and 23 to load it as a view. Here's the background - Redirects table records when a member uses a link and records where they go, and when they return and with what status. Projects table manages the per project information that I need. Currently I do have a third table that keeps a per project count which is updated each time a record is added to the redirects table, however the counts can be a little unreliable. Every hour the server runs the query to fix/verify the counts.
Is there any good way to count the columns without having to use a sum(if(xxx,1,0)) ?
Select projects.ID as ID,cid,name as name,state as status,
sum(if(status="complete",1,0)) as complete,cpc,
cpc*ss as mmkingaku,
cpc*sum(if(status="complete",1,0)) as total,
sum(if(status="screenout",1,0)) as screenout,
sum(if(status="quotafull",1,0)) as quotafull,
sum(if(status="short",1,0)) as short,
sum(if(status="gate",1,0)) as gate,
sum(if(status is null,1,0)) as empty,
sum(if(status="complete",1,0))/(sum(if(status="complete",1,0))+sum(if(status="screenout",1,0)))*100 as IR
from redirects,projects
where redirects.rid=projects.rid and state<>"test" group by name order by cid desc