I have a query that is being used to report on the number of times a trigger in Zabbix, was fired by an event. For example, an event (not enough disk space) fires a trigger that sends an email to someone who can add more disk space.
The query takes about 6 seconds to run
SELECT SQL_NO_CACHE distinct date_format(from_unixtime(a.clock), '%Y - %m') AS Months, count(a.eventid) as NB_Triggers FROM zabbix.events a, zabbix.triggers b where a.objectid = b.triggerid and a.value = 1 group by Months;
I have added SQL_NO_CACHE to avoid using the buffer during my testing
Here is the explain
What I thought might be interesting, was to add a predicate in the WHERE clause on a.clock which is a unix timestamp. Something like events.clock > 1383264000. If I do that, then perhaps I could use the index on that column. The JOIN on the triggers table, somehow causes the query optimizer in MySQL to consider but not use the index on the clock column.
Here is the explain with the clock column in the where clause
If I remove the join on the triggers table, then MySQL uses the events_2 index, but the triggers table is necessary.
My question is, how can I improve this query.
Thank you in advance for any suggestions.