addressing
addressing copied to clipboard
pluginsGLPI
Plugin takes too long
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.
