List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 3 2007 9:27pm
Subject:Re: Need sql optimization help
View as plain text  
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
>>
>>
>>
>>
>
>
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