Re: Performance problem with correlated sub-query

Howard, Steven (US - Tulsa) wrote:

> select servername, databasename, message from messages o where
> o.date_of_msg = (select max(date_of_msg) from messages i where
> i.servername = o.servername);
>
> And this is a dog. It takes 15 – 20 minutes to execute the
> query (there are about 200,000 rows in the table). I have an
> index on (servername, date_of_msg), but it doesn’t seem to
> be used in this query.

Just off the top of my head:

SELECT servername, databasename, message
FROM messages o
WHERE o.date_of_msg = (
  SELECT date_of_msg
  FROM messages i
  WHERE i.servername = o.servername
  ORDER BY date_of_msg
  LIMIT 1
);


HTH,

Mike Mascari