From: Peter Brawley
Date: March 3 2007 7:40pm
Subject: Re: Need sql optimization help
List-Archive: http://lists.mysql.com/mysql/205386
Message-Id: <45E9CF27.3010100@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------050504050302060704060507"

--------------050504050302060704060507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bryan,

A 'Not Exists' query 
<http://www.artfulsoftware.com/infotree/queries.php#41> is usually 
faster when coded as an exclusion join, eg for max-some-value per key, 
left join on the key variable and left.value < right. value where 
left.key is null, so you would need something like ...

SELECT DISTINCT
  e.severity,
  e.time_stamp,
  replace(e.description,'{HOSTNAME}', h.host) AS description,
  h.host,
  h.hostid,
  e.value,
  e.triggerid,
  s.color
FROM fs_events e
JOIN  hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
  ON e.triggerid=e2.triggerid
  AND e.MAX(time_stamp) < e2.MAX(time_stamp)
WHERE e.hostid = 10011
  AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC

PB

Bryan Cantwell wrote:
> I have the following sql that works for what I want to see but is
> terribly slow due to the subquery. Any suggestions on how to get same
> result faster? I have tried group by and cant seem to get the correct
> results that way....
>
> Also is there a product that can help optimize sql and indexing?
>
> SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,
> '{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value,
> e.triggerid, s.color FROM fs_events e, hosts h, fs_severity s WHERE
> e.hostid = 10011 AND e.hostid = h.hostid AND e.time_stamp = (SELECT
> max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid) and
> e.severity = s.severityid ORDER BY e.time_stamp DESC
>
>   

--------------050504050302060704060507--