List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:March 3 2007 8:31pm
Subject:RE: Need sql optimization help
View as plain text  
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
	
	  


Thread
Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
  • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Rolando Edwards2 Mar
    • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Rolando Edwards2 Mar
      • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
        • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Jay Pipes2 Mar
          • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
            • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Jay Pipes2 Mar
              • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
        • Need sql optimization helpBryan Cantwell3 Mar
          • Re: Need sql optimization helpPeter Brawley3 Mar
            • RE: Need sql optimization helpBryan Cantwell3 Mar
              • Re: Need sql optimization helpMichael Dykman3 Mar
                • Re: Need sql optimization helpPeter Brawley3 Mar