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


-- 
 - michael dykman
 - mdykman@stripped
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