Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 2.2.6
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Labels:

      Description

      Terrible Long SQL queries - copying to tmp tables, more than 9 seconds

      SELECT DISTINCT t.triggerid,t.state,t.error,t.url,t.expression,t.description,t.priority,t.lastchange FROM triggers t,functions f,items i,hosts_groups hg WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='11' WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND hg.groupid='20' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.value='1' AND t.flags IN ('0','4') ORDER BY t.lastchange DESC LIMIT 50 OFFSET 0

      SELECT DISTINCT COUNT(DISTINCT t.triggerid) AS rowscount FROM triggers t,functions f,items i,hosts_groups hg WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='11' WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND hg.groupid='7' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.value='1' AND t.flags IN ('0','4')

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                nucleusv Vadim Nesterov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: