MYsql query and DB help

When trying to execute this query my mysql server cpu usage goes to 100% and the page just stalls. I setup an index on (Client_Code, Date_Time, Time_Stamp, Activity_Code, E开发者_如何转开发mployee_Name, ID_Transaction) it doesn't seem to help. What steps can I go about next to fix this issue? Also there is already one index on the database if that matters any. Thanks

> $sql = "SELECT m.Employee_Name, count(m.ID_Transaction)   
>FROM ( SELECT DISTINCT Client_Code FROM Transaction)   
> md JOIN Transaction m ON    
> m.ID_Transaction = ( SELECT  
> ID_Transaction FROM Transaction mi  
> WHERE mi.Client_Code = md.Client_Code AND Date_Time=CURdate() AND Time_Stamp!='' AND 
>  Activity_Code!='000001'  
> ORDER BY m.Employee_Name DESC, mi.Client_Code  DESC, mi.Date_Time DESC,  
> mi.ID_Transaction DESC LIMIT 1 )  
> group by m.Employee_Name";  

I've got a feeling it's due to that > sign next to the AND, what is the expression you're trying to make with that?

Could you express in plain English what results you're after with your query? I've got a feeling this query can be rewritten; that might fix your performance issue. A double join on itself might hurt performance badly.

Something like "the number of transactions per employee for today"?

If that's what you're after, please try this and refine from there:

select m.Employee_Name, count(m.ID_Transaction)
from Transaction m
where m.Date_time=CURdate()
and m.Time_Stamp != ''
and m.Activity_Code != '000001'
group by m.Employee_Name

I Could've sworn I had already posted my response here today....

Why not use a double GROUP BY? I think this is what you were looking for...

SELECT Employee_Name, count(ID_Transaction) FROM Transaction WHERE Date_time=CURdate() AND Time_Stamp != '' AND Activity_Code != '000001' GROUP BY Client_Code, Employee_Name