Vues Mysql temps de traitement tres long. Pourquoi du comment ?

Salut à tous.

Je faisais des requettes SQL via php sur une vue et je viens de voir une différence de temps de traitement de fou furieux (genre plus de 10secondes).

Imaginons que je créer cette vue :

CREATE VIEW ram_multi AS 
SELECT N1.id,N1.id_ag, N1.date, N1.id_tour, N1.com, N1.bloque, N1.nb_c, N1.cc, N1.pds, N1.nom_client, N1.id_client, N1.calend, N1.jours , COUNT( * ) +1 AS nb_tour2
FROM `ram` N1, `ram` N2
WHERE N1.date = N2.date
AND N1.id_ag = N2.id_ag
AND N1.id_client = N2.id_client
AND N1.id <> N2.id
GROUP BY N1.id

Si je fais un select sur cette vue =>

SELECT * FROM `ram_multi` WHERE `date`=20100101 AND `id_ag`='60' AND `id_client`<>'0'  ORDER BY `id_client`,`date`

J'ai 14secondes et des bananes flambées de temps d'execution.

par contre si je fais la requette SANS la vue =>

SELECT N1.id,N1.id_ag, N1.date, N1.id_tour, N1.com, N1.bloque, N1.nb_c, N1.cc, N1.pds, N1.nom_client, N1.id_client, N1.calend, N1.jours , COUNT( * ) +1 AS nb_tour2
FROM `ram` N1, `ram` N2
WHERE N1.date = N2.date
AND N1.id_ag = N2.id_ag
AND N1.id_client = N2.id_client
AND N1.id <> N2.id
AND N1.date=2010-02-24 AND N1.id_ag=60 AND N1.id_client<>0
GROUP BY N1.id

La c'est instantanné.

Je m'interogeais sur le pourquoi du comment car je lis partout que les temps de traitement d'une vue sont ultimes par rapport à une table classique (or vous l'avez compris. Chez moi c'est plutôt l'inverse).

Si vous pouviez éclairer ma lanterne (a titre purement informatif)

Rude
Modifié par ffwrude (24 Feb 2010 - 10:30)

Bonjour,
Je ne sais pas qui t'as conseillé les vues, mais en général c'est une mauvaise solution. Tu peux avoir l'impression (dès fois) que cela te fait gagner du temps, mais en réalité tu vas en perdre à chaque modification d'une des 2 tables concernées. Puisque à chaque modif, il doit refaire la vue.

De plus si tu veux réellement gagner en rapidité, réfléchit plutôt sur comment sont faite tes requêtes, voilà une bonne requête

CREATE VIEW ram_multi AS 

SELECT N1.id,N1.id_ag, N1.date, N1.id_tour, N1.com, N1.bloque, N1.nb_c, N1.cc, N1.pds, N1.nom_client, N1.id_client, N1.calend, N1.jours , COUNT( * ) +1 AS nb_tour2

FROM `ram`as  N1 inner join `ram` N2 on 
   (
       N1.date = N2.date

       AND N1.id_ag = N2.id_ag

       AND N1.id_client = N2.id_client

       AND N1.id <> N2.id
   ) 

GROUP BY N1.id

Et encore ceci n'est rien....
As-tu mis des index sur les champs date, id_ag et id_client ?

En tout cas attention tu vas avoir de mauvaise donnée dans ta table. Tu vas compter plusieurs fois les mêmes lignes.

Des mauvaises données ? Comment ca ?

Les index sont bien mis oui oui.

Je ne connaissais pas les vues. On m'en a parlé recement un peu comme le saint graal. Mais finalement... Je pense que c'est le graal que Bohort à acheter plutôt que l'original.

Peux tu m'expliquer pourquoi tu penses que je vais avoir de mauvaises données ? Je n'ai pas de problème avec les résultats de ma requette.

Je vais tester la tienne pour voir la diff.

EDIT : Après vérification de ta requette j'obtiens éxactement la même chose. même nombre de données (ca c'est normal) et même temps de traitement.

Rude
Modifié par ffwrude (24 Feb 2010 - 12:04)

Salut Rude,

ben je ne suis pas trop d'accord avec masseuro... ou plutôt je nuancerais la réponse : une vue ne devrait pas du tout plomber les temps de réponse en cas de Select. Par contre cela implique qu'elle n'est pas recréée à chaque fois (et vu tes temps de réponse c'est sans doute ce qui se passe) : ce choix de la recréer ou non est fait par défaut par l'optimiseur Mysql sauf si tu lui interdis lors du CREATE VIEW (voir cet article chapitre Vues).

Tu pourrais déjà faire un EXPLAIN pour voir ce qui se passe réellement.

Cela étant dit une vue n'est pas faite pour optimiser les requêtes : en pratique et de ce que j'en ai vu c'est soit pour limiter l'accès à certaines données d'une table, soit pour éviter des SELECT UNION... entre plusieurs tables, soit pour simplifier les traitements (on tape sur cette vue et on s'en fiche de savoir que cela correspond à des champs de n tables différentes).

Sinon l'optimisation SQL est un vaste (et complexe) sujet : voir cet article ou celui-ci.
Modifié par Heyoan (24 Feb 2010 - 12:59)

Plop Heyoan

Bah écolute j'avais déjà regarder du côté de MERGE et TEMPTABLE mais ca n'a rien changer.

Je vais matter EXPLAIN vu que je sais pas du tout ce que c'est.

Après vu que tu m'as filé de la lecture pour au moins deux bonnes heures je reviendrais plus tard pour voir si je choisis de faire kkchoz avec ca ou si je garde ma grosse requette qui elle va très vite. Parce que bon si je passe des heures la dessus je vais me faire taper dessus

Rude

EDIT : Résultats de EXPLAIN sur les deux requette :

1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	111653	Using where; Using filesort
2	DERIVED	n1	ALL	D_date,date	NULL	NULL	NULL	209715	Using temporary; Using filesort
2	DERIVED	n2	ref	D_date,date	date	7	base.n1.date,base.n1.id_ag,base.n1.id_client	1	Using where

et pour la "compliqué" qui elle va très vite :

1	SIMPLE	N1	range	D_date,date	date	7	NULL	538	Using where; Using temporary; Using filesort
1	SIMPLE	N2	ref	D_date,date	date	7	const,const,base.N1.id_client	1	Using where

Mais je n'arrive à bien tout comprendre. Notamment au niveau des tables dérivées.
Modifié par ffwrude (24 Feb 2010 - 14:38)

Ben je suis loin d'être un expert du EXPLAIN et en général je me contente de "deviner" de quoi que ça cause !

Par exemple ici je dirais que le Using temporary veut dire que la vue est systématiquement recréée et donc que si le MERGE ne fonctionne pas (pitêt à cause du GROUP BY ?) il ne faudrait pas utiliser de vue.

Bah écoute je pense que j'ai vais choisir de pas utiliser la vue parce que ca m'a l'air bien galere. Je me contenterai d'utiliser une usine a gaz que je "maitrise" plutôt qu'un truc obscure que je ne connais pas bien.

Le Group By est obligatoire pour moi ici. Sinon ca fait pô ce que je veux. Dommage que je puisse pas détailler plus le truc

Si d'autres ont des avis je suis quand même preneur à titre informatif.

Rude
Modifié par ffwrude (24 Feb 2010 - 15:11)