Requette trop lourd plante easy php

sinifer · Mai 5, 2009, 9:28

Bonjour,

j’ai un gro problème avec cette requete ci dessou qui me fait planté easy php.
je doit fair un affichage qui me sortira les origine, l’effectif et la moyenne de commande, logiquement ma requete marche mais elle est trop lourd je voulai savoir si j’ai pas moyen de l’optimisé

car après 3 minute sa me mets: Fatal error: Maximum execution time of 300 seconds exceeded in C:\Program Files\EasyPHP 3.0\phpmyadmin\libraries\dbi\mysqli.dbi.lib.php on line 164

voici la requete:


SELECT IF(cu.customers_from LIKE '1st%','1rst',cu.customers_from) AS Origin, 
							   COUNT(DISTINCT cu.customers_id) AS effectif, count(co.commandes_status) AS command,
							   AVG(co.commandes_montant) AS Mmc 
								FROM customers cu
								INNER JOIN commandes co 
								ON cu.customers_id=co.customers_id 
								INNER JOIN relances_gratuites rg 
								ON cu.customers_id=rg.customers_id  
								WHERE co.commandes_status='1' 
								AND rg.relances_gratuites_inscription_date>='" . $date_du . "' AND  rg.relances_gratuites_inscription_date<='" . $date_au . "'  
								GROUP BY Origin "

Ce qui est lourd est le inner join relances_gratuites on customers.customers_id=relances_gratuites.customers_id

Mais je suis obligé d’avoir ces trois table car je veu avoir les renseignements qui ce trouve entre ces deux dates, de plus si j’enlève la table commande sa marche très bien donc c’est la preuve que c’est un problème d’optimisation

Es-ce qu’il y a une autre façon de faire ou y-a-t-il une solution pour allegé la requette??

Merci

sinifer · Mai 5, 2009, 11:42

Personne a une idée???

vitamin1981 · Mai 5, 2009, 12:44

Bon désolé par avance mais je ne vais pas chercher à comprendre ce que fais ta requete.

Par contre, voici des éléments qui peuvent te permettre d’avancer :

avant tout, est ce que tu peux nous assurer que la requete correspond bien à ce que tu recherches ?
si tu peux lancer cette requete dans un client Mysql (Squirrel, SQL Developer, Toad, etc.) , indique nous le temps d’exécution. De là, on peut savoir si c’est la requete qui met trop de temps à exécuter, si c’est la machine qui manque de mémoire, si la requete renvoie trop de données etc.
autrement, est ce que ta grosse requête peut être “découpée” en plusieurs requetes simples + programme php ? En l’occurence, tu peux par exemple obtenir tous les effectifs d’un côté, et les origines de l’autre. Ou alors d’abord sélectionner le sous ensemble de commandes avant d’en obtenir les effectifs.

sinifer · Mai 5, 2009, 2:03

Bonjour,

Merci de ta réponse vitamin1981 effectivement cette requette correspond a ce que je cherche.
Le temps d’execution me environ +5minute pour avoir aucun resultat apart

Fatal error: Maximum execution time of 300 seconds exceeded in C:\Program Files\EasyPHP 3.0\phpmyadmin\libraries\dbi\mysqli.dbi.lib.php on line 164

pour la dernière question j’ai pas très bien saisie désolé.

vitamin1981 · Mai 5, 2009, 2:14

Hum, ok, on va dire que c’est ce que tu recherches.

Il y a combien d’enregistrements actuellement dans chacunes des tables ? En particulier les tables aliasées par cu et co.

Autre élément de solution : as tu crée un index sur la table des commandes ? Si oui, sur quel discriminant ?

sinifer · Mai 5, 2009, 2:33

il y a un peu près 340000 enregistrement sur ces deux table
la table commande possède des index mais je sais pas comment voir
j’ai fais un explain et sa ma sortit sa :

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE commandes ALL NULL NULL NULL NULL 382065

vitamin1981 · Mai 5, 2009, 2:49

Jsuis pas un expert des index … mais meme si ca prend plus de place, ca peut améliorer fortement les performances des requetes de base, me semble-t-il.

Dans ton cas, je préconise le découpage. Je suppose que la table qui contient tous les enregistrements est commandes ? Dans ce cas, j’éviterais de la joindre.

C’est surement pas la meilleure solution, mais j’espere que ca te permettra de la trouver

sinifer · Mai 5, 2009, 3:14

ok merci je vais voir commen je vais fair pour la suite vis-a-vi de mon découpage

Sans-Nom · Mai 5, 2009, 8:35

Pour commencer, vérifie les index sur ces champs :

SELECT
   IF(cu.customers_from LIKE '1st%','1rst',cu.customers_from) AS Origin, 
  COUNT(DISTINCT cu.customers_id) AS effectif, 
  count(co.commandes_status) AS command,
  AVG(co.commandes_montant) AS Mmc 
FROM customers cu
INNER JOIN commandes co ON cu.customers_id=co.customers_id 
INNER JOIN relances_gratuites rg 	ON cu.customers_id=rg.customers_id 
WHERE co.commandes_status='1'  AND rg.relances_gratuites_inscription_date>='" . $date_du . "' AND rg.relances_gratuites_inscription_date<='" . $date_au . "' 
GROUP BY Origin "

C’est à dire :
cu.customers_id
co.customers_id
commandes_status (dans ton where)
relances_gratuites_inscription_date (dans ton where)

Si tu as une clef primaire, c’est parfait.

Ensuite, tu devrais extraire la requête exécutée (et pas ce que tu nous donnes) pour voir sur quoi c’est le plus long.

sinifer · Mai 6, 2009, 9:21

Merci Sans_Nom,

j’ai fai mon explain avec cette requete j’espère que comme j’ai procedé c’est juste


explain
 SELECT co.customers_id, cu.customers_id,commandes_status,relances_gratuites_inscription_date
       
FROM customers cu
INNER JOIN commandes co 
ON cu.customers_id=co.customers_id 
INNER JOIN relances_gratuites rg 
ON cu.customers_id=rg.customers_id  
WHERE co.commandes_status='1' 
AND rg.relances_gratuites_inscription_date>='2009-04-26' AND  rg.relances_gratuites_inscription_date<='2009-04-26'

et sa ma mis ce résultat:

Sans-Nom · Mai 6, 2009, 10:33

Soit ça donc :


id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	rg 	ALL 	NULL 	NULL 	NULL 	NULL 	337195 	 
1 	SIMPLE 	co 	ALL 	NULL 	NULL 	NULL 	NULL 	382065 	Using where; Using join buffer
1 	SIMPLE 	cu 	eq_ref 	PRIMARY 	PRIMARY 	4 	tara_voyance.rg.customers_id 	1 	Using where; Using index

Tu ramènes 3371953820654 = 515 321 630 700 lignes, pas étonnant qu’il aime pas (enfin, vérifie je ne sais pas si c’est le nombre total de ligne dans la table, ou après le WHERE). Je dois avouer ne pas trop comprendre les EXPLAIN PLAN tel qu’ils te le donnent, mais je dirais que pour les deux jointures, il ne semble pas utiliser d’INDEX : vérifie que ton customers_id sur les tables commandes, customer, et relances_gratuites aient bien un INDEX.

En revanche, ce n’est pas la même requête que plus haut, et dans celle que tu nous as donné y a des COUNT, GROUP BY : clairement, tu dois ajouter un index pour les colonnes que tu GROUP BY (cf plus bas).

Si je ne me plante pas, la façon dont sont construits les INDEX, est telle que

ils soient déjà triés, ce en fonction du type d’index : pour un index de type RTREE, c’est certain.
si l’index est de type arbre, il y a de fortes chance que le group by soit rapide car il lui suffit de consulter l’index pour avoir le regroupement plutôt que d’aller taper sur toutes les lignes.

Ensuite, il ne faut pas rêver, si tu as une volumétrie importante (si c’est bien 3371953820654 = 515 321 630 700 lignes), tu ne pourras jamais faire mieux en faisant des jointures comme ça :

Le fonctionnement des jointures est globalement binaire. Pour représenter les choses, imagines une opération arithmétique classique comme 456, qui donnerait 20*6 puis 120. Dans n’importe quel langage, tu aurais des variables tampons (les registres) pour stocker les résultats intermédiaires.

Imagine ce qui se passe dans ton cas :

A inner join B inner join C =>
(A inner join B) inner join C =>
TEMP_AB inner join C =>
TEMP_ABC

TEMP_AB est donc une table de 337195*382065 lignes!

Puis tu fais pareil avec TEMP_AB inner join C, et tu obtiens le nombre de lignes que je te donne plus haut.

Je suppose que dans ton cas le serveur écrit à mort sur le disque dur, et que cela ralentis d’autant plus les opérations.

Comme là, tu nous fournis une autre requête, je n’ose imaginer ce que cela donnerait avec la vraie