-
Patch request
-
Resolution: Unresolved
-
Trivial
-
None
-
None
-
None
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.