Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-22052

Suboptimal query generated by getItemsHavingValuesFromSql()

    XMLWordPrintable

Details

    • Patch request
    • Status: Confirmed
    • Trivial
    • Resolution: Unresolved
    • None
    • None
    • Frontend (F)
    • None

    Description

      ui/include/classes/api/managers/CHistoryManager.php defines getItemsHavingValuesFromSql() which generates an SQL query to obtain a list of itemids. The query is suboptimal as it fetches all itemids from a related table instead of the table items. Therefore it has scan many more rows than it needs to and then it has to use GROUP BY to remove duplicates:

      SELECT itemid FROM history_uint WHERE itemid IN (177031,...) AND clock>1670436021 GROUP BY itemid;

      The optimal way of doing this is to SELECT itemids FROM items that meet the given criteria; by definition there will be no duplicates. My suggestion is to use the standard SQL clause WHERE EXISTS, which should result in a semi-join in a nested loop:

      SELECT itemid
      FROM items
      WHERE itemid IN (177031,...)
      AND EXISTS (SELECT 1 FROM history_uint AS i
      WHERE items.itemid = i.itemid
      AND clock>1670436021
      );

      In my case, the query execution time went from 300 ms down to 3 ms, but I have seen the original form take more than 7 seconds, probably due to locking or IO.

      Untested patch attached.

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            pstef Piotr Paweł Stefaniak
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: