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
SQL performance is not usually due to calculations in the select clause. You need to look at the from and group by clauses.
Do your tables have appropriate indexes? You should have an index on redirects.rid, projects.rid, or both. In fact, these should probably be composite indexes, including state and test (wherever is appropriate).
The group by can be a performance hog in MySQL. How much data is in each table?