-
Incident report
-
Resolution: Duplicate
-
Major
-
None
-
1.8.3
Slow query using temporary files. Needs to be optimized to not use temporary files and filesort:
SELECT DISTINCT t.* FROM triggers t,functions f,items i,hosts_groups hg,rights r,users_groups ug WHERE ((t.triggerid BETWEEN 000000000000000 AND 099999999999999)) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND hg.hostid=i.hostid AND r.id=hg.groupid AND r.groupid=ug.usrgrpid AND ug.userid=7 AND r.permission>=2 AND NOT EXISTS( SELECT ff.triggerid FROM functions ff, items ii WHERE ff.triggerid=t.triggerid AND ff.itemid=ii.itemid AND EXISTS( SELECT hgg.groupid FROM hosts_groups hgg, rights rr, users_groups gg WHERE hgg.hostid=ii.hostid AND rr.id=hgg.groupid AND rr.groupid=gg.usrgrpid AND gg.userid=7 AND rr.permission<2)) AND t.lastchange>1284539815 AND (t.priority IN (0,1,2,3,4,5)) AND (t.value IN (1,0)) ORDER BY t.lastchange DESC LIMIT 15 OFFSET 0;
Explain:
----------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | t | range | PRIMARY,triggers_2 | PRIMARY | 8 | NULL | 40263 | Using where; Using temporary; Using fi |
| 1 | PRIMARY | f | ref | functions_1,functions_2 | functions_1 | 8 | zabbix.t.triggerid | 1 | Distinct |
| 1 | PRIMARY | i | eq_ref | PRIMARY,items_1 | PRIMARY | 8 | zabbix.f.itemid | 1 | Distinct |
| 1 | PRIMARY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8 | zabbix.i.hostid | 1 | Using index; Distinct |
| 1 | PRIMARY | r | ref | rights_1,rights_2 | rights_2 | 9 | zabbix.hg.groupid | 1 | Using where; Distinct |
| 1 | PRIMARY | ug | ref | users_groups_1 | users_groups_1 | 16 | zabbix.r.groupid,const | 1 | Using index; Distinct |
| 2 | DEPENDENT SUBQUERY | ff | ref | functions_1,functions_2 | functions_1 | 8 | zabbix.t.triggerid | 1 | |
| 2 | DEPENDENT SUBQUERY | ii | eq_ref | PRIMARY | PRIMARY | 8 | zabbix.ff.itemid | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | hgg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8 | zabbix.ii.hostid | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | rr | ref | rights_1,rights_2 | rights_2 | 9 | zabbix.hgg.groupid | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | gg | ref | users_groups_1 | users_groups_1 | 16 | zabbix.rr.groupid,const | 1 | Using index --- 11 rows in set (0.00 sec) |
- duplicates
-
-
- Closed
-