Steps to reproduce:

• Attempt to delete a template with a large number of hosts in Zabbix

Result:
I received a timeout (after 30 minutes) trying to delete a single template in our Zabbix environment, the template had 0 hosts or templates linked to it, so I expected this to be a quick procedure.
Expected:
The template to be deleted.

I checked the running processes in our DB and found the query to pull out graphs linked tot he template still running.  I believe the issue is due to the query joining with the hosts table, without hosts specified for links to other tables.  I ran the query manually and it took over 1 hour to complete, returning 0 results:

MariaDB [zabbix_infra]> explain SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM graphs g,hosts h WHERE EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='100252') AND g.flags IN ('0','1');

+------+--------------+-------+-------+---------------------------------------------------------+----------------+---------+------------------------+--------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+-------+-------+---------------------------------------------------------+----------------+---------+------------------------+--------+------------------------------+ | 1 | PRIMARY | h | index | NULL | hosts_4 | 386 | NULL | 33957 | Using index; Using temporary | | 1 | PRIMARY | g | ALL | NULL | NULL | NULL | NULL | 314946 | Using where | | 2 | MATERIALIZED | ii | ref | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7 | items_1 | 8 | const | 1 | Using index | | 2 | MATERIALIZED | ggi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | zabbix_infra.ii.itemid | 1 | | +------+--------------+-------+-------+---------------------------------------------------------+----------------+---------+------------------------+--------+------------------------------+ 4 rows 

in set (0.00 sec) MariaDB [zabbix_infra]> SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM graphs g,hosts h WHERE EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='100252') AND g.flags IN ('0','1'); Empty set (1 hour 22 min 1.47 sec)

If I add a hostid condition to the query it returns immediately:

MariaDB [zabbix_infra]> SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM graphs g,hosts h WHERE EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='100252') AND g.flags IN ('0','1') and h.hostid = 100252;

Empty set (0.25 sec)

Would it be possible to add the h.hostid conditions when building this query?

Thanks!

Eric