MySQL Forums
Forum List  »  Spanish

Re: Query rapida y Vista lenta
Posted by: Alex Carol
Date: January 12, 2007 08:30AM

Por cierto MySql 5.0.27.
La query devuelve 41000 registros.

Si, la query directa 0.017 y la vista 4.76.

El plan de ejecución de la query, en principio los indicies són correctos
................................................
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY P ALL 10546
1 PRIMARY F ref LLICENCIA_PERSONA,LLICENCIA_GENET LLICENCIA_PERSONA 8 fch.P.CODIPERSONA 1
1 PRIMARY D eq_ref PRIMARY PRIMARY 8 fch.F.CODIDEFLLICENCIAGENET 1
1 PRIMARY TD ALL PRIMARY 3 Using where
2 DEPENDENT SUBQUERY P ref IDX_CODIFEDERACIOGENET IDX_CODIFEDERACIOGENET 9 fch.F.CODIFEDERACIOGENET 1 Using where
........................
He echo algúna prueva mas y ahora ejecutando la query

SELECT F.CODIFEDERACIOGENET, F.CODIDEFLLICENCIAGENET, F.CODIPERSONA, F.CODIPOBLACIOENV,
F.DIAALTA, F.ANYY, F.DIASOLICITUD, F.IMPORT, F.DESCOMPTE, F.SEGURO,
F.ADRECAENV, F.NUMENV, F.PISENV, F.PORTAENV, F.OBSERVACIONS, F.DIACONFIRMACIO, F.DIAENVIAMENT,
F.DIAANULACIO, F.FORMAPAGAMENT,
D.COMPETIDOR, D.TECNIC, D.EDATMIN, D.EDATMAX,
D.PREUFCH, D.PREUSEGURO, D.PREURFHE, D.TELASSISTENCIA, D.NUMPOLISSA,
TD.DESCRIPCIO, TD.LANGUAGE,
P.CODIFEDERACIO, CONCAT(P.COGNOM1,' ', P.COGNOM2, ', ',P.NOM) AS COGNOMSNOM,
(SELECT SUM(P.IMPORTPAGAT) FROM PAGAMENTSLLGENETS AS P WHERE P.CODIFEDERACIOGENET=F.CODIFEDERACIOGENET) AS TOTALPAGAT
FROM DEFLLICENCIESGENETS AS D, TXTDEFLLICENCIESGENETS AS TD,
PERSONES P , FEDERACIOGENETS AS F
WHERE D.CODIDEFLLICENCIAGENET=F.CODIDEFLLICENCIAGENET AND
P.CODIPERSONA=F.CODIPERSONA AND
D.CODIDEFLLICENCIAGENET=TD.CODIDEFLLICENCIAGENET;
ORDER BY P.CODIPERSONA;
.......
con el order by p.codipersona, me tarda una eternidad, mas de 60 segundos y
el plan de ejecución
.....................
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY D ALL PRIMARY 2 Using temporary; Using filesort
1 PRIMARY TD ref PRIMARY PRIMARY 8 fch.D.CODIDEFLLICENCIAGENET 1
1 PRIMARY F ref LLICENCIA_PERSONA,LLICENCIA_GENET LLICENCIA_GENET 8 fch.D.CODIDEFLLICENCIAGENET 10404
1 PRIMARY P eq_ref PRIMARY PRIMARY 8 fch.F.CODIPERSONA 1
2 DEPENDENT SUBQUERY P ref IDX_CODIFEDERACIOGENET IDX_CODIFEDERACIOGENET 9 fch.F.CODIFEDERACIOGENET 1 Using where
..........


Si alguien sabe que puede passar ...

Muchas gracias.

Options: ReplyQuote


Subject
Views
Written By
Posted
5491
January 12, 2007 02:41AM
3483
January 12, 2007 07:01AM
Re: Query rapida y Vista lenta
3214
January 12, 2007 08:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.