How can i rewrite this query for faster execution
Posted
by
sam
on Stack Overflow
See other posts from Stack Overflow
or by sam
Published on 2011-01-12T08:38:08Z
Indexed on
2011/01/12
8:54 UTC
Read the original article
Hit count: 213
mysql
|mysql-query
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
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
ID Cust_FkId Overall_Rank Service_Duration ProcessID
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
© Stack Overflow or respective owner