-
Problem report
-
Resolution: Fixed
-
Critical
-
3.4.6rc1, 4.0.0alpha1, 4.0 (plan)
-
None
-
Team A
-
Sprint 24
-
1
Housekeeping of events is too slow on MySQL because it's not using index provided, the query is:
select min(clock) from events where events.source=3 and events.object=0 and not exists (select null from problem where events.eventid=problem.eventid or events.eventid=problem.r_eventid)
MySQL is "Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper", running on Ubuntu 16.04.
mysql> explain select min(clock) from events where events.source=3 and events.object=0 and not exists (select null from problem where events.eventid=problem.eventid or events.eventid=problem.r_eventid)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: events
partitions: NULL
type: ref
possible_keys: events_1,events_2
key: events_2
key_len: 8
ref: const,const
rows: 15964577
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: problem
partitions: NULL
type: ALL
possible_keys: PRIMARY,c_problem_2
key: NULL
key_len: NULL
ref: NULL
rows: 708846
filtered: 19.00
Extra: Range checked for each record (index map: 0x9)
2 rows in set, 3 warnings (0,00 sec)
It should be changed to:
mysql> explain select min(clock) from events where events.source=3 and events.object=0 and not exists (select null from problem where events.eventid=problem.eventid) and not exists (select null from problem where events.eventid=problem.r_eventid)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: events
partitions: NULL
type: ref
possible_keys: events_1,events_2
key: events_2
key_len: 8
ref: const,const
rows: 15973658
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: problem
partitions: NULL
type: ref
possible_keys: c_problem_2
key: c_problem_2
key_len: 9
ref: zabbix.events.eventid
rows: 1
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: problem
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: zabbix.events.eventid
rows: 1
filtered: 100.00
Extra: Using index
3 rows in set, 3 warnings (0,00 sec)
