Plugin takes too long

[jsamaniegog]

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.