Plugin takes too long #77
Description
Since the last updates we have noticed that the plugin takes too long.
We have seen that the SQL query that takes time to execute is this
SELECT `port`.`id`,
'NetworkEquipment' AS itemtype,
`dev`.`id` AS on_device,
`dev`.`name` AS dname,
'' AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac`,
`dev`.`users_id`,
INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum
FROM `glpi_networkports` port
LEFT JOIN `glpi_networkequipments` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'NetworkEquipment')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321')
AND `dev`.`is_deleted` = 0
AND `dev`.`is_template` = 0 AND ( `dev`.`entities_id` = '2' ) UNION (SELECT `port`.`id`,
'Computer' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,`dev`.`users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_computers` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'Computer')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'NetworkEquipment' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,`dev`.`users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_networkequipments` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'NetworkEquipment')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'Peripheral' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,`dev`.`users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_peripherals` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'Peripheral')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'Phone' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,`dev`.`users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_phones` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'Phone')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'Printer' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,`dev`.`users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_printers` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'Printer')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'Enclosure' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,0 AS `users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_enclosures` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'Enclosure')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'PDU' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,0 AS `users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_pdus` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'PDU')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' AND `dev`.`is_template` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum) UNION (SELECT `port`.`id`,
'Cluster' AS `itemtype`,
`port`.`items_id`,
`dev`.`name` AS dname,
`port`.`name` AS pname,
`glpi_ipaddresses`.`name` as ip,
`port`.`mac` ,0 AS `users_id` , INET_ATON(`glpi_ipaddresses`.`name`) AS ipnum FROM `glpi_networkports` port
LEFT JOIN `glpi_clusters` dev ON (`port`.`items_id` = `dev`.`id`
AND `port`.`itemtype` = 'Cluster')
LEFT JOIN `glpi_networknames` ON (`port`.`id` = `glpi_networknames`.`items_id`)
LEFT JOIN `glpi_ipaddresses` ON (`glpi_ipaddresses`.`items_id` = `glpi_networknames`.`id`)
WHERE (`glpi_ipaddresses`.`name` IS NOT NULL AND `glpi_ipaddresses`.`name` != '') AND `glpi_ipaddresses`.`version` LIKE 4
AND (INET_ATON(`glpi_ipaddresses`.`name`) BETWEEN '123456789' AND '987654321') AND ( `dev`.`entities_id` = '2' ) AND `dev`.`is_deleted` = '0' GROUP BY `ip`, `port`.`mac` ORDER BY ipnum)
If you run the parts of each UNION ALL separately seems to work faster.
Thank you very much.