How can i rewrite this query for faster execution

[COLOR="blue"]SELECT s1.ID FROM binventory_ostemp s1 JOIN 
( SELECT Cust_FkId, ProcessID, MAX(Service_Duration) AS duration 
 FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' AND Overall_Rank IN 
 (
  SELECT MIN(Overall_Rank) FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' GROUP BY Cust_FkId
 )
 GROUP BY Cust_FkId
) AS s2 ON s1.Cust_FkId = s2.Cust_FkId AND s1.ProcessID=s2.ProcessID 
AND s1.Service_Duration=s2.duration AND s1.ProcessID='4d2d6068678bc' 
GROUP BY s1.Cust_FkId[/COLOR]

Code (markup):

It just goes away if there are more than 10K rows in that table. What it does is find rows for each customer who has min. of overall rank and in those max. of service duration for a given processid

Table Data

[B]ID Cust_FkId  Overall_Rank Service_Duration  ProcessID[/B]
1     23       2            30             4d2d6068678bc
2     23       1            45             4d2d6068678bc
3     23       1            60             4d2d6068678bc
4     56       3            90             4d2d6068678bc
5     56       2            50             4d2d6068678bc
6     56       2            85             4d2d6068678bc

Code (markup):

Result Data

Result ID values must be 3 and 6 only

 

I'm not following this. Could you clarify the problem?

 

I think you're looking for the BETWEEN function. Look up MySQL BETWEEN on Google for some tutorials.

 

ID 2,3 having the min(1) and ID 4 is having the max Serv_Dur(90)..

 

I have not your table structure to test it, but try something like this:

SELECT s1.ID FROM binventory_ostemp s1 JOIN 
(SELECT id, Cust_FkId, ProcessID, Service_Duration AS duration 
 FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' ORDER BY Service_Duration DESC LIMIT 0, 1) s2 ON
 s1.ID = s2.ID
 JOIN
 (SELECT id, Overall_Rank FROM binventory_ostemp WHERE ProcessID='4d2d6068678bc' ORDER BY Overall_Rank  LIMIT 0, 1) s3 ON
 s1.ID = s3ID

Code (markup):