CODEWITHSUNDEEP
mysql
Pradeep
Pradeep

Reputation: 71

Query for self join in MySQL

I have a query which gets the correct result but it is taking 5.5 sec to get the output.. Is there any other way to write a query for this - 

SELECT metricName, metricValue 
FROM Table sm  
WHERE createdtime = (
    SELECT MAX(createdtime) 
    FROM Table b 
    WHERE sm.metricName = b.metricName 
    AND b.sinkName='xx' 
) 
AND sm.sinkName='xx'

Upvotes: 0

Views: 55

Answers (1)

Sirko
Sirko

Reputation: 74106

In your code, the subselect has to be run for every result row of the outer query, which should be quite expensive. Instead, you could select your filter data in a separate query and join both accordingly:

SELECT `metricName`, `metricValue` FROM Table sm 
INNER JOIN (SELECT max(`createdtime`) AS `maxTime, `metricName` from Table b WHERE b.sinkName='xx' GROUP BY `metricName` ) filter
  ON (sm.`createdtime` = filter.`maxTime`) AND ( sm.`metricName` = filter.`metricName`)
WHERE sm.sinkName='xx'

Upvotes: 2

Related Questions