Marah Alh - 2025-03-06

Hello dears,

I need to create a bulk of Requests on iTop, I write an API that:
1- call create API (output fields: id, friendlyname)
2- ApplyStimuls API
I noticed that there is a query in db slow-log scan all UserRequest table (There are about 250000 records).
Is there any recommendation to avoid this behavior?

iTop version is :3.1.1
mariadb version is: 11.5.2
php version: 8.1

# Time: 250218 15:43:07
# User@Host: root[root] @  [127.0.0.6]
# Thread_id: 13568  Schema: itop31  QC_hit: No
# Query_time: 8.127461  Lock_time: 0.001359  Rows_sent: 1  Rows_examined: 248597
# Rows_affected: 0  Bytes_sent: 13889
SET timestamp=1739882587;
SELECT
 test.`id` AS `UserRequestid`,
 `UserRequest_Ticket`.`operational_status` AS `UserRequestoperational_status`,
 `UserRequest_Ticket`.`ref` AS `UserRequestref`,
 `UserRequest_Ticket`.`org_id` AS `UserRequestorg_id`,
 `Organization_org_id`.`name` AS `UserRequestorg_name`,
 `UserRequest_Ticket`.`caller_id` AS `UserRequestcaller_id`,
 CAST(CONCAT(COALESCE(`Person_caller_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_caller_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestcaller_name`,
 `UserRequest_Ticket`.`team_id` AS `UserRequestteam_id`,
 CAST(CONCAT(COALESCE(`Team_team_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestteam_name`,
 `UserRequest_Ticket`.`agent_id` AS `UserRequestagent_id`,
 CAST(CONCAT(COALESCE(`Person_agent_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_agent_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestagent_name`,
 `UserRequest_Ticket`.`title` AS `UserRequesttitle`,
 `UserRequest_Ticket`.`description` AS `UserRequestdescription`,
 `UserRequest_Ticket`.`start_date` AS `UserRequeststart_date`,
 `UserRequest_Ticket`.`end_date` AS `UserRequestend_date`,
 `UserRequest_Ticket`.`last_update` AS `UserRequestlast_update`,
 `UserRequest_Ticket`.`close_date` AS `UserRequestclose_date`,
 `UserRequest_Ticket`.`private_log` AS `UserRequestprivate_log`,
 `UserRequest_Ticket`.`parent_request_id` AS `UserRequestparent_request_id`,
 `UserRequest_Ticket`.`service_id` AS `UserRequestservice_id`,
 `Service_service_id`.`name` AS `UserRequestservice_name`,
 `UserRequest_Ticket`.`servicesubcategory_id` AS `UserRequestservicesubcategory_id`,
 `ServiceSubcategory_servicesubcategory_id`.`name` AS `UserRequestservicesubcategory_name`,
 `Person_agent_id`.`manager_id` AS `UserRequestagent_manager_id`,
 `Person_caller_id`.`section` AS `UserRequestcaller_section`,
 `Person_caller_id`.`unit` AS `UserRequestcaller_unit`,
 `Person_caller_id`.`department` AS `UserRequestcaller_department`,
 `Person_caller_id_Contact`.`phone` AS `UserRequestcaller_phone`,
 `UserRequest_Ticket`.`affecteduser_id` AS `UserRequestaffecteduser_id`,
 `UserRequest_Ticket`.`closed_by` AS `UserRequestclosed_by`,
 CAST(CONCAT(COALESCE(`Person_closed_by`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_closed_by_Contact`.`name`, '')) AS CHAR) AS `UserRequestclosed_by_name`,
 `Person_affecteduser_id`.`section` AS `UserRequestaffecteduser_section`,
 `Person_affecteduser_id`.`unit` AS `UserRequestaffecteduser_unit`,
 `Person_affecteduser_id`.`department` AS `UserRequestaffecteduser_department`,
 `Person_affecteduser_id_Contact`.`phone` AS `UserRequestaffecteduser_phone`,
 CAST(CONCAT(COALESCE(`Person_affecteduser_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_affecteduser_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestaffecteduser_name`,
 `UserRequest_Ticket`.`closure_status` AS `UserRequestclosure_status`,
 `UserRequest_Ticket`.`closure_notes` AS `UserRequestclosure_notes`,
 `UserRequest_Ticket`.`rejection_reason` AS `UserRequestrejection_reason`,
 `UserRequest_Ticket`.`requester_feedback` AS `UserRequestrequester_feedback`,
 `UserRequest_Ticket`.`requester_notes` AS `UserRequestrequester_notes`,
 `UserRequest`.`status` AS `UserRequeststatus`,
 `UserRequest`.`request_type` AS `UserRequestrequest_type`,
 `UserRequest`.`impact` AS `UserRequestimpact`,
 `UserRequest`.`priority` AS `UserRequestpriority`,
 `UserRequest`.`urgency` AS `UserRequesturgency`,
 `UserRequest`.`origin` AS `UserRequestorigin`,
 `UserRequest`.`approver_id` AS `UserRequestapprover_id`,
 `Person_approver_id_Contact`.`email` AS `UserRequestapprover_email`,
 `UserRequest`.`escalation_flag` AS `UserRequestescalation_flag`,
 `UserRequest`.`escalation_reason` AS `UserRequestescalation_reason`,
 `UserRequest`.`assignment_date` AS `UserRequestassignment_date`,
 `UserRequest`.`resolution_date` AS `UserRequestresolution_date`,
 `UserRequest`.`last_pending_date` AS `UserRequestlast_pending_date`,
 `UserRequest`.`cumulatedpending_timespent` AS `UserRequestcumulatedpending`,
 `UserRequest`.`tto_timespent` AS `UserRequesttto`,
 `UserRequest`.`ttr_timespent` AS `UserRequestttr`,
 `UserRequest`.`tto_100_deadline` AS `UserRequesttto_escalation_deadline`,
 `UserRequest`.`tto_100_passed` AS `UserRequestsla_tto_passed`,
 `UserRequest`.`tto_100_overrun` AS `UserRequestsla_tto_over`,
 `UserRequest`.`ttr_100_deadline` AS `UserRequestttr_escalation_deadline`,
 `UserRequest`.`ttr_100_passed` AS `UserRequestsla_ttr_passed`,
 `UserRequest`.`ttr_100_overrun` AS `UserRequestsla_ttr_over`,
 `UserRequest`.`time_spent` AS `UserRequesttime_spent`,
 `UserRequest`.`resolution_code` AS `UserRequestresolution_code`,
 `UserRequest`.`solution` AS `UserRequestsolution`,
 `UserRequest`.`pending_reason` AS `UserRequestpending_reason`,
 `UserRequest`.`parent_incident_id` AS `UserRequestparent_incident_id`,
 `Incident_parent_incident_id_Ticket`.`ref` AS `UserRequestparent_incident_ref`,
 `UserRequest`.`parent_problem_id` AS `UserRequestparent_problem_id`,
 `Problem_parent_problem_id_Ticket`.`ref` AS `UserRequestparent_problem_ref`,
 `UserRequest`.`parent_change_id` AS `UserRequestparent_change_id`,
 `Change_parent_change_id_Ticket`.`ref` AS `UserRequestparent_change_ref`,
 `UserRequest`.`public_log` AS `UserRequestpublic_log`,
 `UserRequest`.`user_satisfaction` AS `UserRequestuser_satisfaction`,
 `UserRequest`.`user_commment` AS `UserRequestuser_comment`,
 `UserRequest`.`Attach_description` AS `UserRequestAttach_description`,
 `UserRequest`.`temp_id` AS `UserRequesttemp_id`,
 `UserRequest`.`closure_details` AS `UserRequestclosure_details`,
 `UserRequest_Ticket`.`finalclass` AS `UserRequestfinalclass`,
 CAST(CONCAT(COALESCE(`UserRequest_Ticket`.`ref`, '')) AS CHAR) AS `UserRequestfriendlyname`,
 CAST(CONCAT(COALESCE(`Organization_org_id`.`name`, '')) AS CHAR) AS `UserRequestorg_id_friendlyname`,
 COALESCE((`Organization_org_id`.`status` = 'inactive'), 0) AS `UserRequestorg_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Person_caller_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_caller_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestcaller_id_friendlyname`,
 COALESCE((`Person_caller_id_Contact`.`status` = 'inactive'), 0) AS `UserRequestcaller_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Team_team_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestteam_id_friendlyname`,
 COALESCE((`Team_team_id_Contact`.`status` = 'inactive'), 0) AS `UserRequestteam_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Person_agent_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_agent_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestagent_id_friendlyname`,
 COALESCE((`Person_agent_id_Contact`.`status` = 'inactive'), 0) AS `UserRequestagent_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`UserRequest_parent_request_id_Ticket`.`ref`, '')) AS CHAR) AS `UserRequestparent_request_id_friendlyname`,
 CAST(CONCAT(COALESCE(`Service_service_id`.`name`, '')) AS CHAR) AS `UserRequestservice_id_friendlyname`,
 CAST(CONCAT(COALESCE(`ServiceSubcategory_servicesubcategory_id`.`name`, '')) AS CHAR) AS `UserRequestservicesubcategory_id_friendlyname`,
 CAST(CONCAT(COALESCE(`Person_manager_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_manager_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestagent_manager_id_friendlyname`,
 COALESCE((`Person_manager_id_Contact`.`status` = 'inactive'), 0) AS `UserRequestagent_manager_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Person_affecteduser_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_affecteduser_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestaffecteduser_id_friendlyname`,
 COALESCE((`Person_affecteduser_id_Contact`.`status` = 'inactive'), 0) AS `UserRequestaffecteduser_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Person_closed_by`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_closed_by_Contact`.`name`, '')) AS CHAR) AS `UserRequestclosed_by_friendlyname`,
 COALESCE((`Person_closed_by_Contact`.`status` = 'inactive'), 0) AS `UserRequestclosed_by_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Person_approver_id`.`first_name`, ''), COALESCE(' ', ''), COALESCE(`Person_approver_id_Contact`.`name`, '')) AS CHAR) AS `UserRequestapprover_id_friendlyname`,
 COALESCE((`Person_approver_id_Contact`.`status` = 'inactive'), 0) AS `UserRequestapprover_id_obsolescence_flag`,
 CAST(CONCAT(COALESCE(`Incident_parent_incident_id_Ticket`.`ref`, '')) AS CHAR) AS `UserRequestparent_incident_id_friendlyname`,
 CAST(CONCAT(COALESCE(`Problem_parent_problem_id_Ticket`.`ref`, '')) AS CHAR) AS `UserRequestparent_problem_id_friendlyname`,
 CAST(CONCAT(COALESCE(`Change_parent_change_id_Ticket`.`ref`, '')) AS CHAR) AS `UserRequestparent_change_id_friendlyname`,
 `Change_parent_change_id_Ticket`.`finalclass` AS `UserRequestparent_change_id_finalclass_recall`,
 `UserRequest`.`cumulatedpending_started` AS A`UserRequestcumulatedpending_started`,
 `UserRequest`.`cumulatedpending_laststart` AS `UserRequestcumulatedpending_laststart`,
 `UserRequest`.`cumulatedpending_stopped` AS `UserRequestcumulatedpending_stopped`,
 `UserRequest`.`tto_started` AS `UserRequesttto_started`,
 `UserRequest`.`tto_laststart` AS `UserRequesttto_laststart`,
 `UserRequest`.`tto_stopped` AS `UserRequesttto_stopped`,
 `UserRequest`.`tto_75_deadline` AS `UserRequesttto_75_deadline`,
 `UserRequest`.`tto_75_passed` AS `UserRequesttto_75_passed`,
 `UserRequest`.`tto_75_triggered` AS `UserRequesttto_75_triggered`,
 `UserRequest`.`tto_75_overrun` AS `UserRequesttto_75_overrun`,
 `UserRequest`.`tto_100_deadline` AS `UserRequesttto_100_deadline`,
 `UserRequest`.`tto_100_passed` AS `UserRequesttto_100_passed`,
 `UserRequest`.`tto_100_triggered` AS `UserRequesttto_100_triggered`,
 `UserRequest`.`tto_100_overrun` AS `UserRequesttto_100_overrun`,
 `UserRequest`.`ttr_started` AS `UserRequestttr_started`,
 `UserRequest`.`ttr_laststart` AS `UserRequestttr_laststart`,
 `UserRequest`.`ttr_stopped` AS `UserRequestttr_stopped`,
 `UserRequest`.`ttr_75_deadline` AS `UserRequestttr_75_deadline`,
 `UserRequest`.`ttr_75_passed` AS `UserRequestttr_75_passed`,
 `UserRequest`.`ttr_75_triggered` AS `UserRequestttr_75_triggered`,
 `UserRequest`.`ttr_75_overrun` AS `UserRequestttr_75_overrun`,
 `UserRequest`.`ttr_100_deadline` AS `UserRequestttr_100_deadline`,
 `UserRequest`.`ttr_100_passed` AS `UserRequestttr_100_passed`,
 `UserRequest`.`ttr_100_triggered` AS `UserRequestttr_100_triggered`,
 `UserRequest`.`ttr_100_overrun` AS `UserRequestttr_100_overrun`,
 `UserRequest`.`public_log_index` AS `UserRequestpublic_log_index`,
 `UserRequest_Ticket`.`private_log_index` AS `UserRequestprivate_log_index`
 FROM 
   `ticket_request` AS `UserRequest`
   LEFT JOIN (
      `person` AS `Person_approver_id` 
      INNER JOIN 
         `contact` AS `Person_approver_id_Contact`
       ON `Person_approver_id`.`id` = `Person_approver_id_Contact`.`id`
   ) ON `UserRequest`.`approver_id` = `Person_approver_id`.`id`
   LEFT JOIN 
      `ticket` AS `Incident_parent_incident_id_Ticket`
    ON `UserRequest`.`parent_incident_id` = `Incident_parent_incident_id_Ticket`.`id`
   LEFT JOIN 
      `ticket` AS `Problem_parent_problem_id_Ticket`
    ON `UserRequest`.`parent_problem_id` = `Problem_parent_problem_id_Ticket`.`id`
   LEFT JOIN 
      `ticket` AS `Change_parent_change_id_Ticket`
    ON `UserRequest`.`parent_change_id` = `Change_parent_change_id_Ticket`.`id`
   INNER JOIN (
      `ticket` AS `UserRequest_Ticket` 
      INNER JOIN 
         `organization` AS `Organization_org_id`
       ON `UserRequest_Ticket`.`org_id` = `Organization_org_id`.`id`
      LEFT JOIN (
         `person` AS `Person_caller_id` 
         INNER JOIN 
            `contact` AS `Person_caller_id_Contact`
          ON `Person_caller_id`.`id` = `Person_caller_id_Contact`.`id`
      ) ON `UserRequest_Ticket`.`caller_id` = `Person_caller_id`.`id`
      LEFT JOIN 
         `contact` AS `Team_team_id_Contact`
       ON `UserRequest_Ticket`.`team_id` = `Team_team_id_Contact`.`id`
      LEFT JOIN (
         `person` AS `Person_agent_id` 
         LEFT JOIN (
            `person` AS `Person_manager_id` 
            INNER JOIN 
               `contact` AS `Person_manager_id_Contact`
             ON `Person_manager_id`.`id` = `Person_manager_id_Contact`.`id`
         ) ON `Person_agent_id`.`manager_id` = `Person_manager_id`.`id`
         INNER JOIN 
            `contact` AS `Person_agent_id_Contact`
          ON `Person_agent_id`.`id` = `Person_agent_id_Contact`.`id`
      ) ON `UserRequest_Ticket`.`agent_id` = `Person_agent_id`.`id`
      LEFT JOIN 
         `ticket` AS `UserRequest_parent_request_id_Ticket`
       ON `UserRequest_Ticket`.`parent_request_id` = `UserRequest_parent_request_id_Ticket`.`id`
      LEFT JOIN 
         `service` AS `Service_service_id`
       ON `UserRequest_Ticket`.`service_id` = `Service_service_id`.`id`
      LEFT JOIN 
         `servicesubcategory` AS `ServiceSubcategory_servicesubcategory_id`
       ON `UserRequest_Ticket`.`servicesubcategory_id` = `ServiceSubcategory_servicesubcategory_id`.`id`
      INNER JOIN (
         `person` AS `Person_affecteduser_id` 
         INNER JOIN 
            `contact` AS `Person_affecteduser_id_Contact`
          ON `Person_affecteduser_id`.`id` = `Person_affecteduser_id_Contact`.`id`
      ) ON `UserRequest_Ticket`.`affecteduser_id` = `Person_affecteduser_id`.`id`
      LEFT JOIN (
         `person` AS `Person_closed_by` 
         INNER JOIN 
            `contact` AS `Person_closed_by_Contact`
          ON `Person_closed_by`.`id` = `Person_closed_by_Contact`.`id`
      ) ON `UserRequest_Ticket`.`closed_by` = `Person_closed_by`.`id`
   ) ON `UserRequest`.`id` = `UserRequest_Ticket`.`id`
 WHERE ((((((CAST(CONCAT(COALESCE(`UserRequest_Ticket`.`ref`, '')) AS CHAR) = 'R-228952') AND COALESCE((`Team_team_id_Contact`.`finalclass` IN ('Team')), 1)) AND COALESCE((`UserRequest_parent_request_id_Ticket`.`finalclass` IN ('UserRequest')), 1)) AND COALESCE((`Incident_parent_incident_id_Ticket`.`finalclass` IN ('Incident')), 1)) AND COALESCE((`Problem_parent_problem_id_Ticket`.`finalclass` IN ('Problem')), 1)) AND COALESCE((`Change_parent_change_id_Ticket`.`finalclass` IN ('RoutineChange', 'ApprovedChange', 'NormalChange', 'EmergencyChange', 'Change')), 1))
 ORDER BY `UserRequestref` DESC;