Incidencia - General - Falta de índices

[enricsinergia]

Descripción del problema
Se ha dectado algunas consultas que pueden ser cuello de botella ante un aluvión de peticiones al CRM.
En concreto se han detectado 2 nuevos puntos de conflicto:

1. La consulta que se realiza por número de identificación cuando llega un nuevo formulario.
2. La consulta que se ejecuta para comprobar si un FdT ya se ha ejecutado sobre un registro

Cómo reproducir el problema

1. En una instancia con gran número de registros en Contacts y en aow_processed probar las 2 consultas y ver que los tiempos de consulta no son inmediatos.

Comportamiento esperado
Unas consultas tan repetidas deben tener un tiempo de respuesta óptimo.

Solución propuesta
Crear los índices adecuados.
El problema se solucionó temporalmente, ejecutando:
create index eps_identification_number on contacts_cstm(stic_identification_number_c);
create index eps_identification_number on accounts_cstm(stic_identification_number_c);
create index eps_workflowId_parentId on aow_processed(aow_workflow_id, parent_id, deleted);

En el caso de aow_processed la revisión debe ir más allá ya que hay 2 índices definidos (aow_processed_index_workflow y aow_processed_index_workflow_id) redundantes y que no cubren este caso necesario.

Contexto
La consulta sobre Contacts se identificó en el log de errores en una instancia en un momento de alta demanda (anuncio en TV).
L aconsulta sobre aow_processed se detectó con el SHOW FULL PROCESSLISXT mientras se monitoreaba la instancia en pleno aluvión.

Información adicional

1. Consulta de búsuqeda por número de identificación:
   SELECT contacts.* , contacts_cstm.fecha_alta_socio_actual_c, contacts_cstm.complementaria_182_c, contacts_cstm.fecha_creatividad_c, contacts_cstm.fecha_alta_socio_c, contacts_cstm.stic_alt_address_county_c, contacts_cstm.stic_alt_address_region_c, contacts_cstm.stic_alt_address_type_c, contacts_cstm.stic_age_c, contacts_cstm.stic_acquisition_channel_c, contacts_cstm.stic_do_not_send_postal_mail_c, contacts_cstm.stic_employment_status_c, contacts_cstm.stic_gender_c, contacts_cstm.stic_identification_number_c, contacts_cstm.stic_identification_type_c, contacts_cstm.stic_language_c, contacts_cstm.stic_postal_mail_return_reason_c, contacts_cstm.stic_preferred_contact_channel_c, contacts_cstm.stic_primary_address_county_c, contacts_cstm.stic_primary_address_region_c, contacts_cstm.stic_primary_address_type_c, contacts_cstm.stic_professional_sector_c, contacts_cstm.stic_professional_sector_other_c, contacts_cstm.stic_referral_agent_c, contacts_cstm.stic_relationship_type_c, contacts_cstm.stic_tax_name_c, contacts_cstm.stic_total_annual_donations_c, contacts_cstm.stic_182_error_c, contacts_cstm.stic_182_excluded_c, contacts_cstm.stic_professional_profile_c, contacts_cstm.sepe_benefit_perceiver_c, contacts_cstm.sepe_disability_c, contacts_cstm.sepe_education_level_c, contacts_cstm.sepe_immigrant_c, contacts_cstm.sepe_insertion_difficulties_c, contacts_cstm.stic_incorpora_locations_id_c, contacts_cstm.inc_address_block_c, contacts_cstm.inc_address_district_c, contacts_cstm.inc_address_door_c, contacts_cstm.inc_address_floor_c, contacts_cstm.inc_address_num_a_c, contacts_cstm.inc_address_num_b_c, contacts_cstm.inc_address_postal_code_c, contacts_cstm.inc_address_street_c, contacts_cstm.inc_address_street_type_c, contacts_cstm.inc_car_use_c, contacts_cstm.inc_children_c, contacts_cstm.inc_collectives_c, contacts_cstm.inc_communications_language_c, contacts_cstm.inc_country_c, contacts_cstm.inc_derivation_c, contacts_cstm.inc_disability_cert_id_c, contacts_cstm.inc_disability_degree_c, contacts_cstm.inc_disabled_children_c, contacts_cstm.inc_driving_licenses_c, contacts_cstm.inc_economic_benefits_c, contacts_cstm.inc_employment_status_c, contacts_cstm.inc_employ_office_reg_time_c, contacts_cstm.inc_geographical_proximity_c, contacts_cstm.inc_id_c, contacts_cstm.inc_incorporation_date_c, contacts_cstm.inc_incorpora_record_c, contacts_cstm.inc_job_characteristics_c, contacts_cstm.inc_lopd_consent_c, contacts_cstm.inc_max_commuting_time_c, contacts_cstm.inc_nationality_c, contacts_cstm.inc_observations_c, contacts_cstm.inc_own_vehicle_c, contacts_cstm.inc_people_in_charge_c, contacts_cstm.inc_requested_employment_c, contacts_cstm.inc_requested_employment_det_c, contacts_cstm.inc_requested_workday_c, contacts_cstm.inc_travel_availability_c, contacts_cstm.inc_unwanted_employments_c, contacts_cstm.inc_synchronization_log_c, contacts_cstm.inc_synchronization_errors_c, contacts_cstm.inc_synchronization_date_c, contacts_cstm.inc_reference_group_c, contacts_cstm.inc_reference_entity_c, contacts_cstm.inc_reference_officer_c, contacts_cstm.inc_state_code_c, contacts_cstm.inc_municipality_code_c, contacts_cstm.inc_town_code_c, contacts_cstm.inc_state_c, contacts_cstm.inc_municipality_c, contacts_cstm.inc_town_c, contacts_cstm.origen_del_colaborador_socio_c, contacts_cstm.utm_campaign_alta_socio_c, contacts_cstm.email_altas_sin_formulario_c, contacts_cstm.ultimo_contacto_clari_impaga_c, contacts_cstm.ultimo_contacto_aumento_cuot_c, contacts_cstm.lugar_captacion_f2f_c, contacts_cstm.campo_pedro_c, contacts_cstm.cnewsletter_c, contacts_cstm.mitre_c, contacts_cstm.origen_del_contacto_c, contacts_cstm.utm_campaign_c, contacts_cstm.wp_enviado_c, contacts_cstm.llamada_bienvenida_c, contacts_cstm.creado_para_identificar_dupl_c, contacts_cstm.ultimo_envio_conversion_c, contacts_cstm.resultado_clari_impagados_c, contacts_cstm.dorsal_c, contacts_cstm.presentacion_hacienda_enero_c, contacts_cstm.fecha_envio_conversion_firma_c, contacts_cstm.tipologia_de_lead_c, contacts_cstm.origen_conversion_call_cente_c, contacts_cstm.llamada_gracias_c, contacts_cstm.error_laura_c, contacts_cstm.fecha_sincronizar_ac_c, contacts_cstm.microaccion_c, contacts_cstm.origen_bbdd_uecm_c, contacts_cstm.resultadoconversionexternali_c, contacts_cstm.contacto_pirata_c, contacts_cstm.contacto_empresas_alex_c, contacts_cstm.actualizar_ac_c, contacts_cstm.pagina_dorsal_c, contacts_cstm.origen_del_colaborador_donan_c, contacts_cstm.resultado_contacto_aumento_c, contacts_cstm.account_id_c, contacts_cstm.actualizacion_c, contacts_cstm.alta_por_cambio_de_titular_c, contacts_cstm.alta_recuperada_c, contacts_cstm.cacepto_c, contacts_cstm.canal_alta_donante_c, contacts_cstm.canal_alta_socio_c, contacts_cstm.contact_id_c, contacts_cstm.content_alta_socio_c, contacts_cstm.content_c, contacts_cstm.creatividad_c, contacts_cstm.datos_erroneos_m182_c, contacts_cstm.error_f_de_pago_c, contacts_cstm.estado_colaborador_socio_c, contacts_cstm.etiquetacion_actualizada_c, contacts_cstm.facebook_c, contacts_cstm.flujo_emails_conversion_c, contacts_cstm.ga_user_id_c, contacts_cstm.grupo_test_emailing_c, contacts_cstm.id_importacion_c, contacts_cstm.linkedin_c, contacts_cstm.llamada_conversion_directa_c, contacts_cstm.llamada_conversion_flujo_c, contacts_cstm.medium_alta_socio_c, contacts_cstm.medium_c, contacts_cstm.no_desea_certificado_c, contacts_cstm.no_desea_comunicaciones_c, contacts_cstm.password_c, contacts_cstm.project_id_c, contacts_cstm.proyecto_corre_campaign_c, contacts_cstm.proyecto_corre_retos_c, contacts_cstm.registro_falso_c, contacts_cstm.resto_direccion_alternativa_c, contacts_cstm.resto_direccion_principal_c, contacts_cstm.subcanal_alta_socio_c, contacts_cstm.subcanal_de_procedencia_c, contacts_cstm.twitter_c, contacts_cstm.ucm_tags_c, contacts_cstm.uecm_tag_periodista_c, contacts_cstm.username_c, LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name, ''), ' ', IFNULL(contacts.last_name, '')))) as name , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name, ''), ' ', IFNULL(contacts.last_name, '')))) as full_name, ' ' account_name , ' ' account_id , ' ' opportunity_role_fields , ' ' opportunity_id , jt5.last_name report_to_name , jt5.assigned_user_id report_to_name_owner , 'Contacts' report_to_name_mod , jt6.name campaign_name , jt6.assigned_user_id campaign_name_owner , 'Campaigns' campaign_name_mod, ' ' c_accept_status_fields , ' ' call_id , ' ' m_accept_status_fields , ' ' meeting_id , ' ' e_invite_status_fields , ' ' fp_events_contactsfp_events_ida , ' ' e_accept_status_fields , jt11.name inc_location_c , ' ' stic_centers_contacts_name , ' ' stic_centers_contactsstic_centers_ida , jt13.name ucm_organizcontacto_c , LTRIM(RTRIM(CONCAT(IFNULL(jt14.first_name, ''), ' ', IFNULL(jt14.last_name, '')))) ucm_perscontacto_c , jt15.name ucm_proyectocontacto_c FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c LEFT JOIN users jt0 ON contacts.modified_user_id = jt0.id AND jt0.deleted = 0 AND jt0.deleted = 0 LEFT JOIN users jt1 ON contacts.created_by = jt1.id AND jt1.deleted = 0 AND jt1.deleted = 0 LEFT JOIN users jt2 ON contacts.assigned_user_id = jt2.id AND jt2.deleted = 0 AND jt2.deleted = 0 LEFT JOIN contacts jt5 ON contacts.reports_to_id = jt5.id AND jt5.deleted = 0 AND jt5.deleted = 0 LEFT JOIN campaigns jt6 ON contacts.campaign_id = jt6.id AND jt6.deleted = 0 AND jt6.deleted = 0 LEFT JOIN stic_incorpora_locations jt11 ON contacts_cstm.stic_incorpora_locations_id_c = jt11.id AND jt11.deleted = 0 LEFT JOIN accounts jt13 ON contacts_cstm.account_id_c = jt13.id AND jt13.deleted = 0 LEFT JOIN contacts jt14 ON contacts_cstm.contact_id_c = jt14.id AND jt14.deleted = 0 LEFT JOIN project jt15 ON contacts_cstm.project_id_c = jt15.id AND jt15.deleted = 0 where (stic_identification_number_c = 'XXXXXXXX') AND contacts.deleted = 0 ORDER BY name

2. Consulta de FdT
   SELECT aow_processed.* FROM aow_processed WHERE aow_workflow_id = 'XXXX-XXX-XXX' AND parent_id = 'XXXX-XXX-XXX' AND deleted=0 LIMIT 0,1