TurnKey Linux Virtual Appliance Library

Dead query

Hi: I spent a lot of time creating a query, and it doesn´t return an error, but I run it and doesn´t start. I left it for 23 hours (more than enough, I think) and it didn´t get one single row. I checked with other program (Pentaho spoon, that lets you follow the operation).
The query has a lot of joins, but I wrote some with more, with no problems.
I´d really appreciate any advise on which part is slowing it down
here goes the query:

SELECT
P.apellido
, P.nombre
, CONCAT(P.apellido,", ",P.nombre) AS apellido_nombre
, TDOC.tipo_documento
, P.numero_documento
, P.sexo
, P.cuil
, CONCAT(DOM.calle,IFNULL(DOM.numero,''),IFNULL(DOM.piso,''),IFNULL(DOM.dpto,''),IFNULL(DOM.adicional,''),IFNULL(DOM.codigo_postal,'')) AS domicilio_personal
, LOCP.localidad AS localidad_particular
, PARTP.partido AS partido_particular
, PROVP.provincia AS provincia_particular
, PAP.pais AS pais_particular
, CASE
WHEN (MONTH(P.fecha_nacimiento) < MONTH(CURRENT_DATE)) THEN YEAR(CURRENT_DATE) - YEAR(P.fecha_nacimiento)
WHEN (MONTH(P.fecha_nacimiento) = MONTH(CURRENT_DATE)) AND (DAY(P.fecha_nacimiento) <= DAY(CURRENT_DATE)) THEN YEAR(CURRENT_DATE) - YEAR(P.fecha_nacimiento)
ELSE (YEAR(CURRENT_DATE) - YEAR(P.fecha_nacimiento)) - 1
END AS edad
,P.fecha_nacimiento

,CO.denominacion
, C.comision
, 'BECAS' as tipo_comision
,PUNT.orden_merito
, CA_D.recomendacion AS ca_d_recomendacion
-- ,TD.tipo_decision AS Directorio
,IF(LIME.tope_edad < YEAR( FROM_DAYS(TO_DAYS(CO.fecha_desde)-TO_DAYS(P.fecha_nacimiento))), 'CON EXCEPCION' , NULL) AS excepcion_por_edad

,P.telefono_personal AS telefono_part
,CONCAT(IFNULL(LT.telefono_pais,''),"-",IFNULL(LT.telefono_area,''),"-", IFNULL(LT.telefono_caracteristica,''),"-",LT.telefono_numero," int ",IFNULL(LT.telefono_interno,'')) AS telefono_Lab
,P.email_personal AS email_personal
,LT.email AS 'email-lab'
/* ,PCE.dato AS PCEDATO
,PCT.dato AS PCTTDATO*/

, GA.gran_area AS GRAN_AREA
, DIS.disciplina AS DIS_PRI
, DIS2.disciplina AS DIS_SEC
, DISDES.disciplina_desagregada AS DIS_DESAGREGADA
, DISDES.codigo AS DIS_DESAGREGADA_COD
, DISDES2.disciplina_desagregada AS DIS_DESAGREGADA_SEC
, DISDES2.codigo AS DIS_DESAGREGADA_SEC_COD

, org.unidad AS Lugar_trabajo
, UO1.unidad AS nivel_1
, UO2.unidad AS nivel_2
, UO3.unidad AS nivel_3
, UO4.unidad AS nivel_4
, LOC.localidad AS LOCALIDAD_LT
, PART.partido AS PARTIDO_LT
, PROV.provincia AS PROVINCIA_LT
, PROV.codigo AS COD_PROVINCIA_LT
, PA. pais AS PAIS_LT
, PA. codigo_pais AS COD_PAIS_LT

, E.estado AS estado_tramite


FROM PERSONA P
INNER JOIN TIPO_DOCUMENTO TDOC ON (P.tipo_documento_tk=TDOC.tk)
LEFT JOIN DOMICILIO DOM ON (DOM.persona_tk=P.tk)
LEFT JOIN PAIS PAP ON (PAP.tk=DOM.pais_tk)
LEFT JOIN LOCALIDAD LOCP ON (LOCP.tk=DOM.localidad_tk)
LEFT JOIN PARTIDO PARTP ON (LOCP.partido_tk=PARTP.tk)
LEFT JOIN PROVINCIA PROVP ON (PARTP.provincia_tk=PROVP.tk)

INNER JOIN TRAMITE T ON (P.propietario_tk=T.propietario_tk)
LEFT JOIN ESTADO E ON (E.tk = T.estado_tk)
INNER JOIN LUGAR_TRABAJO_TRAMITE LTT ON (T.tk = LTT.tramite_tk)
INNER JOIN LUGAR_TRABAJO LT ON (LTT.lugar_trabajo_tk = LT.tk)
INNER JOIN CONVOCATORIA CO ON (CO.tk = T.convocatoria_tk)
INNER JOIN COMISION_TRAMITE CT ON (T.tk = CT.tramite_tk)
INNER JOIN COMISION C ON (CT.comision_tk = C.tk AND C.tipo_comision_tk=4)
LEFT JOIN OBJETO_EVALUACION OBJE ON CO.objeto_evaluacion_tk=OBJE.tk
LEFT JOIN LIMITE_EDAD LIME ON LIME.objeto_evaluacion_tk=OBJE.tk

LEFT JOIN ORDEN_MERITO_TRAMITE PUNT ON (PUNT.tramite_tk = T.tk)

LEFT JOIN
(SELECT
CT.tramite_tk
, CD.tk AS comision_dictamen_tk
, CD.tipo_dictamen_tk
,CASE
WHEN CD.tipo_recomendacion_tk = 14
THEN 'RECO.'
WHEN CD.tipo_recomendacion_tk = 13
THEN 'NO RECO.'
ELSE ''
END AS recomendacion ,
CD.tipo_recomendacion_tk
FROM TRAMITE T
INNER JOIN COMISION_TRAMITE CT ON (T.tk = CT.tramite_tk)
INNER JOIN COMISION C ON (CT.comision_tk = C.tk)
LEFT JOIN COMISION_DICTAMEN CD ON (CT.tk = CD.comision_tramite_tk)
WHERE (T.tk = CT.tramite_tk)
AND (CD.tipo_dictamen_tk = 1)
AND (C.tipo_comision_tk <> 2)
AND ( CD.estado_comision_dictamen_tk IN (4,6))) CA_D ON (CA_D.tramite_tk = CT.tramite_tk)

INNER JOIN DATO_ACADEMICO_TRAMITE DAT ON (T.tk = DAT.tramite_tk)
LEFT JOIN DISCIPLINA DIS ON (DIS.tk = DAT.disciplina_tk)
LEFT JOIN DISCIPLINA_DESAGREGADA DISDES ON (DISDES.tk = DAT.disciplina_desagregada_tk)
LEFT JOIN DISCIPLINA DIS2 ON (DIS2.tk = DAT.disciplina_sec_tk)
LEFT JOIN DISCIPLINA_DESAGREGADA DISDES2 ON (DISDES2.tk = DAT.disciplina_desagregada_sec_tk)
LEFT JOIN GRAN_AREA AS GA ON (DIS.gran_area_tk=GA.tk)

LEFT JOIN DIRECTOR_TRAMITE_PRESENTADO DT ON (DT.tramite_tk = T.tk )
LEFT JOIN DIRECTOR_PRESENTADO DIR ON (DIR.tk = DT.director_tk AND DIR.tipo_director_tk = 1)
LEFT JOIN UNIDAD_ORGANIZATIVA org ON (org.tk = LT.unidad_organizativa_tk)
LEFT JOIN UNIDAD_ORGANIZATIVA UO1 ON (UO1.tk = SUBSTR(org.unidad, 1, 7))
LEFT JOIN UNIDAD_ORGANIZATIVA UO2 ON (UO2.tk = SUBSTR(org.unidad, 9, 7))
LEFT JOIN UNIDAD_ORGANIZATIVA UO3 ON (UO3.tk = SUBSTR(org.unidad, 17, 7))
LEFT JOIN UNIDAD_ORGANIZATIVA UO4 ON (UO4.tk = SUBSTR(org.unidad, 25, 7))
LEFT JOIN LOCALIDAD LOC ON (org.localidad_tk=LOC.tk)
LEFT JOIN PARTIDO PART ON (LOC.partido_tk=PART.tk)
LEFT JOIN PROVINCIA PROV ON (PART.provincia_tk=PROV.tk)
LEFT JOIN PAIS PA ON (PROV.pais_tk=PA.tk)

WHERE C.tipo_comision_tk <> 2
AND LIME.objeto_evaluacion_tk IS NOT NULL
AND CO.tk IN (104201102,103201102,105201102,104201101,103201101,105201101)
ORDER BY C.comision,CO.tk,PUNT.orden_merito;

Thank you

Jeremy's picture

May be worth posting to a forum with more MySQL experts

You may get a response here, but I suspect that your request is above the skillset of most of the users (at least the active ones) here. I'm sure you could find a number of other forums to post your question on (perhaps even the MySQL forums themselves?)

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <strike> <caption>

More information about formatting options

Leave this field empty. It's part of a security mechanism.
(Dear spammers: moderators are notified of all new posts. Spam is deleted immediately)