These helped me with performance on the web GUI...
create index items_hostid on items (hostid);
for queries like this:
SELECT DISTINCT g.groupid,g.name,n.name as node_name FROM groups g,nodes n,hosts_groups hg,hosts h WHERE (g.groupid IN (400100000000005,400100000000003,400100000000001,4 00400000000005,400100000000002,400100000000004,400 400000000001,400400000000004,500500000000006,50050 0000000010,500500000000005,500500000000011,5005000 00000008,500500000000002,500500000000007,500500000 000001,500500000000003,500500000000004,20020000000 0013,200200000000008,200200000000005,2002000000000 12,200200000000007,200200000000010,200200000000006 ,200200000000001,100100000000027,100100000000026,1 00100000000028,100100000000007,100100000000008,100 100000000009,100100000000001,300300000000006,30030 0000000005,300100000000007,300300000000007,3003000 00000008,300100000000006)) AND n.nodeid=(g.groupid div 100000000000000) AND h.status=0 AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND EXISTS (SELECT i.hostid FROM items i WHERE hg.hostid=i.hostid ) ORDER BY node_name,g.name;
and
create index rights_perm on rights (permission);
for queries like this:
SELECT DISTINCT g.* FROM graphs g,graphs_items gi,items i,hosts_groups hg,rights r,users_groups ug,hosts h WHERE ((g.graphid BETWEEN 400000000000000 AND 499999999999999)) AND gi.graphid=g.graphid AND i.itemid=gi.itemid AND hg.hostid=i.hostid AND r.id=hg.groupid AND r.groupid=ug.usrgrpid AND ug.userid=100100000000002 AND r.permission>=2 AND NOT EXISTS( SELECT gii.graphid FROM graphs_items gii, items ii WHERE gii.graphid=g.graphid AND gii.itemid=ii.itemid AND EXISTS( SELECT hgg.groupid FROM hosts_groups hgg, rights rr, users_groups ugg WHERE ii.hostid=hgg.hostid AND rr.id=hgg.groupid AND rr.groupid=ugg.usrgrpid AND ugg.userid=100100000000002 AND rr.permission<2)) AND g.graphid=gi.graphid AND h.hostid=i.hostid AND h.status<>3;
I was getting poor performance as we have quite a few systems that just sit there and show graphs and the dashboard, etc. We were seeing poor performance that was negatively impacting our DB. I turned on slow query log and found these sped things up a quite a bit.
create index items_hostid on items (hostid);
for queries like this:
SELECT DISTINCT g.groupid,g.name,n.name as node_name FROM groups g,nodes n,hosts_groups hg,hosts h WHERE (g.groupid IN (400100000000005,400100000000003,400100000000001,4 00400000000005,400100000000002,400100000000004,400 400000000001,400400000000004,500500000000006,50050 0000000010,500500000000005,500500000000011,5005000 00000008,500500000000002,500500000000007,500500000 000001,500500000000003,500500000000004,20020000000 0013,200200000000008,200200000000005,2002000000000 12,200200000000007,200200000000010,200200000000006 ,200200000000001,100100000000027,100100000000026,1 00100000000028,100100000000007,100100000000008,100 100000000009,100100000000001,300300000000006,30030 0000000005,300100000000007,300300000000007,3003000 00000008,300100000000006)) AND n.nodeid=(g.groupid div 100000000000000) AND h.status=0 AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND EXISTS (SELECT i.hostid FROM items i WHERE hg.hostid=i.hostid ) ORDER BY node_name,g.name;
and
create index rights_perm on rights (permission);
for queries like this:
SELECT DISTINCT g.* FROM graphs g,graphs_items gi,items i,hosts_groups hg,rights r,users_groups ug,hosts h WHERE ((g.graphid BETWEEN 400000000000000 AND 499999999999999)) AND gi.graphid=g.graphid AND i.itemid=gi.itemid AND hg.hostid=i.hostid AND r.id=hg.groupid AND r.groupid=ug.usrgrpid AND ug.userid=100100000000002 AND r.permission>=2 AND NOT EXISTS( SELECT gii.graphid FROM graphs_items gii, items ii WHERE gii.graphid=g.graphid AND gii.itemid=ii.itemid AND EXISTS( SELECT hgg.groupid FROM hosts_groups hgg, rights rr, users_groups ugg WHERE ii.hostid=hgg.hostid AND rr.id=hgg.groupid AND rr.groupid=ugg.usrgrpid AND ugg.userid=100100000000002 AND rr.permission<2)) AND g.graphid=gi.graphid AND h.hostid=i.hostid AND h.status<>3;
I was getting poor performance as we have quite a few systems that just sit there and show graphs and the dashboard, etc. We were seeing poor performance that was negatively impacting our DB. I turned on slow query log and found these sped things up a quite a bit.