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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
Sent: Saturday, March 03, 2007 1:40 PM
To: Bryan Cantwell
Cc: [email protected]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
