How to optimize and speed up this query against the MySQL Zabbix database?

Question

MySQL

I am building a web application around a Zabbix (MySQL) database, I would like to optimize and speed up the following query:

```
SELECT distinct(hosts.hostid), max(CONVERT(CONCAT(events.eventid, events.value, events.severity) , UNSIGNED))
FROM hosts
INNER JOIN hosts_groups ON hosts.hostid = hosts_groups.hostid
INNER JOIN hstgrp ON hosts_groups.groupid = hstgrp.groupid

INNER JOIN items ON hosts.hostid = items.hostid
INNER JOIN functions ON items.itemid = functions.itemid
INNER JOIN events ON functions.triggerid = events.objectid
WHERE events.name = %s
AND hstgrp.groupid = %s
AND hosts.status != 3 # 3 - not templates
GROUP BY hosts.hostid;
```

I am making 7 such requests within a single http GET request to a Flask based web application. The total time of these 7 requests varies from 10 to 70 seconds, which I attribute to the fact that the events table is constantly changing. The events table contains a large number of entries.

Results Explain:



How can I speed up this query?

Now I am adding Redis to store the results of these requests for 5 minutes, then I will probably add Celery to remove such long time requests from the http server. But this is about speeding up the application as a whole.

Answer 1

The 2nd line in EXPLAIN says that the DBMS must go through all 958 thousand records in order to select the ones needed by the where clause. As far as I understand, WHERE events.name = %s.
I propose to make a selection not by the name field, but by the one that has an index. Or create an index on name and other fields that are searched.
You can also use partitioning of the events table.
If this is not enough, then due to heavy queries to the DBMS, it makes sense to create an additional table that is updated by the task scheduler. It will be a materialized view optimized for web application requests.