Sorry for the typo, lose the MAX! ...
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.time_stamp < e2.time_stamp
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
PB
Michael Dykman wrote:
> Your inner query guarantees that MySQL will have to test an awfull lot
> of combinations: (# of records in fs_events)^2 * (# of records in
> hosts) * (# of records in severity)
>
> (SELECT max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid)
> needs to be recalculated every time we try to match a row in the outer
> query.. that is going to hurt a lot.
>
> - michael dykman
>
>
> On 3/3/07, Bryan Cantwell <bcantwell@stripped> wrote:
>> Gives me invalid use of group function:
>>
>>
>>
>> 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 MAX(e.time_stamp) < MAX(e2.time_stamp)
>>
>> WHERE e.hostid = 10011
>>
>> AND e2.triggerid IS NULL
>>
>> ORDER BY e.time_stamp DESC
>>
>>
>> ________________________________
>>
>> From: Peter Brawley [mailto:peter.brawley@stripped]
>> Sent: Saturday, March 03, 2007 1:40 PM
>> To: Bryan Cantwell
>> Cc: mysql@stripped
>> Subject: Re: Need sql optimization help
>>
>>
>> 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
>>
>>
>>
>>
>
>
