host.get api method SQL improvement

While debuging our DB performance, we encountered a SQL statement that were executed 23869 times in one day. It used 44,9% of all time that all queries that took over 1 second that day.

Turns out that this query is used for host.get method (frontends/php/include/classes/api/services/CHost.php), and is probably called for most pages that any user may call every day.

A sample of the query is this:

SELECT DISTINCT h.hostid,h.name,h.status FROM hosts h,hosts_groups hg WHERE h.flags IN (0,4) AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('21','45') WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND hg.groupid='174' AND hg.hostid=h.hostid AND h.status=0 AND EXISTS (SELECT NULL FROM items i,functions f,triggers t WHERE h.hostid=i.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND i.status=0 AND t.status=0 AND t.flags IN (0,4)) 

Environment details:
– hosts 18352
– hosts_groups 99820
– rights 3179
– items 1330422
– functions 495390
– triggers 279145

Suggestions and discussions in the comments.