Problem: "host.create" API method take too long time on large enviroments (>5000)
Solution: Columns "hostid" and "templateid" need individual INDEXES on "host_templates" table

–

I'm syncing my CMDB system with Zabbix using API mode. On first charge, I need create 10000 hosts and this process was taking too long. Looking into MySQL's process list, I got this query:

SELECT max(ht.hostid) as hostid, ht.templateid
FROM(
SELECT count(htt.templateid) as ccc, htt.hostid
FROM hosts_templates htt
WHERE htt.hostid NOT IN (
SELECT httt.templateid FROM hosts_templates httt
)
GROUP BY htt.hostid
) ggg, hosts_templates ht
WHERE ggg.ccc>1 AND ht.hostid=ggg.hostid
GROUP BY ht.templateid

This query is used on host.create - file api/classes/class.ctemplate.php in line 1651

Running this query manually took 30 seconds (10000 hosts linked). EXPLAIN method show me that some code was running without indexes.

The solution was create individual indexes for "hostid" and "templateid", because the original schema only offers a unique key with both columns.

ALTER TABLE `hosts_templates` ADD INDEX ( `hostid` )
ALTER TABLE `hosts_templates` ADD INDEX ( `templateid` )