MySQL Forums
Forum List  »  Newbie

Re: Help with Index
Posted by: Peter Brawley
Date: August 08, 2016 08:59AM

Explain can't find an index for t, and suggests the query engine will need to examine 129456*36091*36091 or about 169 trillion rows. Too many.

Moving the correlated subqueries from the Select list to the From clause should cut that number down by six orders of magnitude or more, even with the inequality join ...

select 
  t.eventid,
  mine.mineventid as eventid,
  t1.triggerid,
  t1.priority,
  t3.itemid,
  t3.key_,
  fcItemMapping(t3.key_) as key_id,
  t4.hostid,
  fcEventType(t.eventid) as event_type,
  fcEventExpurgo(t.eventid) as event_expurgo,
  t.clock as clock_down,
  ifnull(minc.minclock,unix_timestamp()) as clock_up,
from zabbix.events t
join (                 -- SUBQUERY FOR EVENTID
  select object_id, min(event_id) as mineventid
  from zabbix.events 
  where value=0
  group by object_id
) as mine using(object_id)
join (                 -- SUBQUERY FOR CLOCK
  select object_id, mineventid, min(clock) as minclock
  from zabbix.events 
  where value=0
  group by object_id
) as minc on t.object_id=minc.object_id and t.clock<minc.clock
join zabbix.triggers t1  on ( t.objectid = t1.triggerid and t1.status = 0 )
join zabbix.functions t2 on ( t1.triggerid = t2.triggerid )
join zabbix.items t3     on ( t3.itemid = t2.itemid )
join zabbix.hosts t4     on ( t4.hostid = t3.hostid )
where t.ns <> 0 and t.source = 0 and t.value = 1 and t.eventid > 12943169

A rule of thumb for covering indexes is: Where clauses first, then Join and Select columns, and as Rick suggests, equality comparisons before inequality comparisons. That would suggest an events covering index(source,value, ns,eventid,clock,object_id). Start experimenting and let Explain's response guide you. Be sure to turn off caching by setting query_cache_size and/or query_cache_type=0.

Options: ReplyQuote


Subject
Written By
Posted
August 02, 2016 08:52AM
August 03, 2016 08:03AM
August 03, 2016 10:24AM
August 03, 2016 01:18PM
August 03, 2016 03:08PM
August 05, 2016 08:08AM
Re: Help with Index
August 08, 2016 08:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.