You are not logged in.
Bonjour,
Nous nous sommes aperçu sur notre installation que lors de l'appel à la méthode en webservice glpi.getTicket, cette méthode appelle la fonction getDocForItem.
La requête sql qui est générée est la suivante :
SELECT * FROM `glpi_documents` WHERE `id` IN (SELECT `documents_id` FROM `glpi_documents_items` WHERE `itemtype` = 'Ticket' AND `items_id` = '1659954');Sur notre instance, la commande show full processlist nous montre que cette requête est en preparing parfois pendant plus de 30 secondes.
Dans l'exemple qui suit, nous avons la requête suivante qui nous retourne un résultat au bout de 3 secondes (tout dépend de la charge au moment du lancement de la requête) :
(root@localhost) [glpi]> SELECT * FROM `glpi_documents` WHERE `id` IN (SELECT `documents_id` FROM `glpi_documents_items` WHERE `itemtype` = 'Ticket' AND `items_id` = '1659954');
...
XX rows in set (3.92 sec)Quand on fait un profile sur cette requête :
(root@localhost) [glpi]> show profile ;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000032 |
| checking query cache for query | 0.000102 |
| Opening tables | 0.000020 |
| System lock | 0.000008 |
| Table lock | 0.000064 |
| init | 0.000079 |
| optimizing | 0.000013 |
| statistics | 0.000017 |
| preparing | 0.000014 |
| executing | 0.000005 |
| Sending data | 0.000293 |
| optimizing | 0.000021 |
| statistics | 0.000506 |
| preparing | 3.920311 |
| end | 0.000012 |
| end | 0.000003 |
| query end | 0.000003 |
| storing result in query cache | 0.000003 |
| freeing items | 0.000012 |
| closing tables | 0.000005 |
| logging slow query | 0.000002 |
| logging slow query | 0.000038 |
| cleaning up | 0.000005 |
+--------------------------------+----------+
23 rows in set (0.00 sec)On se rend compte que tout le temps est consommé par l'étape preparing.
Quand on fait un explain de cette requête, cela nous renvoie ceci :
(root@localhost) [glpi]> explain SELECT * FROM `glpi_documents` WHERE `id` IN (SELECT `documents_id` FROM `glpi_documents_items` WHERE `itemtype` = 'Ticket' AND `items_id` = '1659954');
+----+--------------------+----------------------+-----------------+---------------+---------+---------+------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------+-----------------+---------------+---------+---------+------------------+--------+--------------------------+
| 1 | PRIMARY | glpi_documents | ALL | NULL | NULL | NULL | NULL | 229959 | Using where |
| 2 | DEPENDENT SUBQUERY | glpi_documents_items | unique_subquery | unicity,item | unicity | 310 | func,const,const | 1 | Using index; Using where |
+----+--------------------+----------------------+-----------------+---------------+---------+---------+------------------+--------+--------------------------+
2 rows in set (0.00 sec)On voit ici que la requête ayant pour id 1 est de type ALL et n'utilise pas d'index.
Je vous propose donc de remplacer cette requête par la requête suivante :
SELECT * FROM `glpi_documents` WHERE `id` IN (SELECT `documents_id` FROM `glpi_documents_items` WHERE `itemtype` = 'Ticket' AND `items_id` = '1659954');Cela permet d'avoir le même résultat mais en 0,02 secondes :
(root@localhost) [glpi]> SELECT * FROM `glpi_documents` LEFT JOIN glpi_documents_items ON documents_id=glpi_documents.id WHERE `itemtype` = 'Ticket' AND `items_id` = '1659954';
...
XX rows in set (0.02 sec)Quand on regarde un profile de cette requête :
(root@localhost) [glpi]> show profile ;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000032 |
| checking query cache for query | 0.000082 |
| Opening tables | 0.000022 |
| System lock | 0.000007 |
| Table lock | 0.000040 |
| init | 0.000072 |
| optimizing | 0.000026 |
| statistics | 0.000558 |
| preparing | 0.000027 |
| executing | 0.000005 |
| Sending data | 0.020249 |
| end | 0.000019 |
| end | 0.000006 |
| query end | 0.000006 |
| storing result in query cache | 0.000434 |
| freeing items | 0.000024 |
| closing tables | 0.000009 |
| logging slow query | 0.000005 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
19 rows in set (0.00 sec)Quand on fait un explain dessus :
(root@localhost) [glpi]> explain SELECT * FROM `glpi_documents` LEFT JOIN glpi_documents_items ON documents_id=glpi_documents.id WHERE `itemtype` = 'Ticket' AND `items_id` = '16 59954';
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+------+-------------+
| 1 | SIMPLE | glpi_documents_items | ref | unicity,item | item | 306 | const,const | 140 | Using where |
| 1 | SIMPLE | glpi_documents | eq_ref | PRIMARY | PRIMARY | 4 | glpi.glpi_documents_items.documents_id | 1 | |
+----+-------------+----------------------+--------+---------------+---------+---------+----------------------------------------+------+-------------+
2 rows in set (0.01 sec)Maintenant la requête sur la table glpi_documents est de type eq_ref. On utilise les index, et on gagne en rapidité d’exécution.
Ci dessous une proposition de patch permettant d'améliorer les performances de la fonction getDocForItem.
diff --git a/inc/methodtools.class.php b/inc/methodtools.class.php
index c2e14a7..1c4be90 100644
--- a/inc/methodtools.class.php
+++ b/inc/methodtools.class.php
@@ -134,10 +134,9 @@ class PluginWebservicesMethodTools extends PluginWebservicesMethodCommon {
$id2name = false;
}
$query .= "FROM `glpi_documents`
- WHERE `id` IN (SELECT `documents_id`
- FROM `glpi_documents_items`
- WHERE `itemtype` = '".$item->getType()."'
- AND `items_id` = '".$item->getID()."')";
+ LEFT JOIN `glpi_documents_items` ON `glpi_documents_items`.`documents_id` = `glpi_documents`.`id`
+ WHERE `itemtype` = '".$item->getType()."'
+ AND `items_id` = '".$item->getID()."'";
$resp = array();
foreach ($DB->request($query) as $data) {Vous en pensez quoi ?
Offline
Bonjour,
Une nouvelle version du patch en n'utilisant plus un LEFT JOIN car cela remonte en plus les champs de la table glpi_documents_items qui ne sont pas utiles tout en conservant l'amélioration des performances.
diff --git a/inc/methodtools.class.php b/inc/methodtools.class.php
index c2e14a7..d3e0f78 100644
--- a/inc/methodtools.class.php
+++ b/inc/methodtools.class.php
@@ -128,16 +128,15 @@ class PluginWebservicesMethodTools extends PluginWebservicesMethodCommon {
global $DB;
if (Session::getLoginUserID()) {
- $query = "SELECT * ";
+ $query = "SELECT `glpi_documents`.* ";
} else {
- $query = "SELECT `id`, `name`, `filename`, `mime` ";
+ $query = "SELECT `glpi_documents`.`id`, `glpi_documents`.`name`, `glpi_documents`.`filename`, `glpi_documents`.`mime` ";
$id2name = false;
}
- $query .= "FROM `glpi_documents`
- WHERE `id` IN (SELECT `documents_id`
- FROM `glpi_documents_items`
- WHERE `itemtype` = '".$item->getType()."'
- AND `items_id` = '".$item->getID()."')";
+ $query .= "FROM `glpi_documents`, `glpi_documents_items`
+ WHERE `glpi_documents`.`id` = `glpi_documents_items`.`documents_id`
+ AND `glpi_documents_items`.`itemtype` = '".$item->getType()."'
+ AND `glpi_documents_items`.`items_id` = '".$item->getID()."'";
$resp = array();
foreach ($DB->request($query) as $data) {Cordialement,
Offline