Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

enter image description here

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

enter image description here

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.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.