From: Peter Brawley Date: March 3 2007 9:27pm Subject: Re: Need sql optimization help List-Archive: http://lists.mysql.com/mysql/205393 Message-Id: <45E9E85B.7070004@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 >> 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 >> >> >> >> > >