Optimisation de requete SQL

**dark_vidor** · 14/01/2006, 22h29

Après avoir lu http://sqlpro.developpez.com/cours/optimiser/ et avoir plus ou moins appliquer a mes requetes ... je me demande si on peut encore faire mieux en terme d'utilisation des resources pour le serveur ...

Voici la suite des requete qui vont me servir dans la prochaine version de mon jeu pour mettre les achats des joueurs a jour (ce bout de code est censé tout me mettre a jour

)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

- trop dense voir plus bas -

Ma question est simple est ce qu'on peut faire encore plus light

Pour la version actuel j'utilisait des boucles et plein de variables/constante php la j'en utilise beaucoup moins

ça devrait etre encore mieux ?

Quelqu'un peut il m'aider

Merci par avance

**pcaboche** · 14/01/2006, 23h19

Envoyé par dark_vidor

Ma question est simple est ce qu'on peut faire encore plus light

Oui on peut : tu enlèves toutes tes requêtes imbriquées et à la place, tu apprends à faire des jointures.

En effet, les requêtes imbriquées ne sont non seulement pas supportées par toutes les versions de MySQL mais en plus font perdre pas mal de temps.

Aussi, tu en profites pour indenter ton code, parce que c'est franchement illisible.

**dark_vidor** · 14/01/2006, 23h21

on indente comment du mysql

je sais pas trop ou mettre mes retours a la ligne :s

comment on fait des jointures ?

et creer une table temporaire ou je met tout mes resultats c'est possible ?

notement pour tout les sum() qui ne sont pas faisable autrement que par un select

**pcaboche** · 15/01/2006, 00h29

Envoyé par dark_vidor

comment on fait des jointures ?

Simple: tu prends un bouquin de SQL, tu l'ouvres, tu lis la première lettre, puis la deuxième, puis la troisième... et ainsi de suite jusqu'à ce que tu rencontres un espace. Toutes les lettres lues forment un mot, ensuite, tu recommences et tu t'apercevras que ça formera un nouveau mot. Ensuite, tu continues, et tes mots formeront des phrase, tes phrases des paragraphes, tes paragraphes des chapitres.

Si tu arrives à la dernière page, forcément tu auras trouvé comment faire une jointure. Sinon, c'est que ton bouquin n'était pas bon alors tu recommences avec un autre, en espérant qu'il soit meilleur que le précédent...

***

Envoyé par dark_vidor

on indente comment du mysql

je sais pas trop ou mettre mes retours a la ligne :s

comment on fait des jointures ?

Pour toutes ces 2 questions, la réponse est du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
$update_players = mysql_query("
 
   UPDATE players
 
   LEFT OUTER JOIN planetes
   ON players.id = planetes.players
 
 
   LEFT OUTER JOIN achats
   ON players.id = achats.planetes
 
   LEFT OUTER JOIN attaques A
   ON players.id = attaques.planetes
 
   SET playersrestes = 
         players.restes
      + SUM(planetes.salaire)
 
      - CEIL (
         ( SUM(A.u11) 
           + SUM(A.u12)
           + SUM(A.u13)
           + SUM(A.u14)
           + SUM(A.u15)
           + SUM(A.u16)
           + SUM(A.u21)
          )
      ) * ".$ent_ter_1."
 
      ... /* Trop long ton truc, j'en ai marre ! */
 
 
   WHERE players.id = " .$id_players[0] ."
 
   GROUP BY players.id ; "
 
) or die(mysql_error()) ;

Tu trouves pas que c'est plus clair que ta soupe? En plus, il y a des chances que ce soit plus efficace.

Envoyé par dark_vidor

notement pour tout les sum() qui ne sont pas faisable autrement que par un select

Ah ouais? Tu te souviens de cette scène dans "L'Empire Contre-Attaque" quand Luke dit à Maître Yoda que c'est impossible de sortir le vaisseau du marais? Et bien Maître Yoda, c'est moi !

**pcaboche** · 15/01/2006, 11h06

Et comme je suis pas sûr que tu puisse faire un GROUP BY dans un UPDATE, en fait, tu fais un SELECT imbriqué (mais un seul, pas 4 !) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
$update_players = mysql_query("
 
   UPDATE players P1
 
   SET P1.restes =
   (
    SELECT    
      P2.restes
      + SUM(planetes.salaire)
 
      - CEIL (
         ( SUM(A.u11) 
           + SUM(A.u12)
           + SUM(A.u13)
           + SUM(A.u14)
           + SUM(A.u15)
           + SUM(A.u16)
           + SUM(A.u21)
          )
      ) * ".$ent_ter_1."
 
      ... /* Trop long ton truc, j'en ai marre ! */
 
    FROM players P2
 
    LEFT OUTER JOIN planetes
    ON P2.id = planetes.players
 
 
    LEFT OUTER JOIN achats
    ON P2.id = achats.planetes
 
    LEFT OUTER JOIN attaques A
    ON P2.id = attaques.planetes
 
    WHERE P1.id = P2.id
    GROUP BY P2.id
  )
 
 
  WHERE P1.id = " .$id_players[0] 
 
) or die(mysql_error()) ;

Edit: corrigé une erreur.

**dark_vidor** · 15/01/2006, 12h06

oula j'sui a la masse je pige pas grand chose meme avec les docs

vous pouvez expliquer

**pcaboche** · 15/01/2006, 12h54

Tout est expliqué là: http://sql.developpez.com/sqlaz/jointures/#L2.2

T'as pas besoin de tout lire, juste les jointures interne (INNER JOIN) et externe gauche (LEFT OUTER JOIN) et de comprendre la différence entre les deux.

**dark_vidor** · 15/01/2006, 13h31

merci, j'avais lu la doc pourtant ... mais apres une re-relecture ça va mieux

et le group by id, il permet de tout faire d'un coup ou alors faut continuer de faire une bloucle php ?

**pcaboche** · 15/01/2006, 15h24

Envoyé par dark_vidor

merci, j'avais lu la doc pourtant ... mais apres une re-relecture ça va mieux

et le group by id, il permet de tout faire d'un coup ou alors faut continuer de faire une bloucle php ?

J'ai corrigé une erreur de copier/coller dans le script précédent (j'ai un gros rhume et donc du mal à réfléchir)

Dans la requête précédente, tu peux te passer du GROUP BY, mais j'aime bien le mettre quand on a des fonctions d'aggrégation (SUM, COUNT...), sauf dans une requête de type:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) FROM unetable

D'abord, essaye le SELECT séparément pour voir si ça marche. Après, tu l'intégreras dans l'UPDATE.

**dark_vidor** · 15/01/2006, 15h33

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
//Création du tableau des joueurs
$array_players = mysql_query("SELECT id FROM players WHERE team != 3 AND activate = 1");
 
while ($id_players = mysql_fetch_row($array_players))
{
	if(substr($id_players[0], 0, 1) == 1)
	$update_players = mysql_query("
	UPDATE players
		SET restes = 
		(
			SELECT
				players.restes
				+ SUM(planetes.salaire)
				- SUM(achats.total)
				- CEIL(
				  (
					SUM(attaques.u11) +
					SUM(attaques.u12) +
					SUM(attaques.u13) +
					SUM(attaques.u14) +
					SUM(attaques.u15) +
					SUM(attaques.u16) +
					SUM(attaques.u21)
				  )*".$ent_ter_1.")
				- CEIL(
				  (
					SUM(attaques.u31) +
					SUM(attaques.u32) +
					SUM(attaques.u33)
				  )*".$ent_cha_1.")
				- SUM(attaques.u41)*".$ent_141."
				- SUM(attaques.u42)*".$ent_142."
				- SUM(attaques.u43)*".$ent_143."
				- SUM(attaques.u44)*".$ent_144."
 
			FROM players
			LEFT OUTER JOIN planetes ON players.id = planetes.players
			LEFT OUTER JOIN achats ON players.id = achats.planetes
			LEFT OUTER JOIN attaques ON players.id = attaques.planetes
			WHERE players.id = " .$id_players[0] ."
			GROUP BY players.id
		)
	)");
 
	if(substr($id_players[0], 0, 1) == 2){}
	$update_players = mysql_query("
	UPDATE players
		SET restes = 
		(
			SELECT
				players.restes
				+ SUM(planetes.salaire)
				- SUM(achats.total)
				- CEIL(
				  (
					SUM(attaques.u11) +
					SUM(attaques.u12) +
					SUM(attaques.u13) +
					SUM(attaques.u14) +
					SUM(attaques.u15) +
					SUM(attaques.u16) +
					SUM(attaques.u21)
				  )*".$ent_ter_2.")
				- CEIL(
				  (
					SUM(attaques.u31) +
					SUM(attaques.u32) +
					SUM(attaques.u33)
				  )*".$ent_cha_2.")
				- SUM(attaques.u41)*".$ent_241."
				- SUM(attaques.u42)*".$ent_242."
				- SUM(attaques.u43)*".$ent_243."
				- SUM(attaques.u44)*".$ent_244."
 
			FROM players
			LEFT OUTER JOIN planetes ON players.id = planetes.players
			LEFT OUTER JOIN achats ON players.id = achats.planetes
			LEFT OUTER JOIN attaques ON players.id = attaques.planetes
			WHERE players.id = " .$id_players[0] ."
			GROUP BY players.id
		)
	)");
 
	//mysql_free_result($update_players);
}

ben ça me donne ça ... pk renommez le nom des tables ?

Bon j'ai tester y'a pas d'erreur de syntaxe maintenant faut que je lui teste des calculs

**dark_vidor** · 15/01/2006, 20h07

au final ça donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
//Création du tableau des joueurs
$array_players = mysql_query("SELECT id FROM players WHERE team != 3 AND activate = 1");
 
while ($id_players = mysql_fetch_row($array_players))
{
	$sum_s = mysql_fetch_row(mysql_query("SELECT SUM(salaire) FROM planetes WHERE players = ".$id_players[0]));
 
	$sum_a = mysql_fetch_row(mysql_query("SELECT SUM(total) FROM achats WHERE players = ".$id_players[0]));
 
	if(substr($id_players[0], 0, 1) == 1)
	{
		$req_e = mysql_query("
		SELECT
			CEIL(
			(
				SUM(u11)*".$e_111." +
				SUM(u12)*".$e_112." +
				SUM(u13)*".$e_113." +
				SUM(u14)*".$e_114." +
				SUM(u15)*".$e_115." +
				SUM(u16)*".$e_116." +
				SUM(u21)
			)*".$ent_ter_1.")
			+ CEIL(
			(
				SUM(u31) +
				SUM(u32) +
				SUM(u33)
			)*".$ent_cha_1.")
			+ SUM(u41)*".$ent_141."
			+ SUM(u42)*".$ent_142."
			+ SUM(u43)*".$ent_143."
			+ SUM(u44)*".$ent_144."
		FROM attaques
		WHERE players = ".$id_players[0]);
		$sum_e = mysql_fetch_row($req_e);
	}
 
	if(substr($id_players[0], 0, 1) == 2)
	{
		$req_e = mysql_query("
		SELECT
			CEIL(
			(
				SUM(u11)*".$e_211." +
				SUM(u12)*".$e_212." +
				SUM(u13)*".$e_213." +
				SUM(u14)*".$e_214." +
				SUM(u15)*".$e_215." +
				SUM(u16)*".$e_216." +
				SUM(u21)
			)*".$ent_ter_2.")
			+ CEIL(
			(
				SUM(u31) +
				SUM(u32) +
				SUM(u33)
			)*".$ent_cha_2.")
			+ SUM(u41)*".$ent_241."
			+ SUM(u42)*".$ent_242."
			+ SUM(u43)*".$ent_243."
			+ SUM(u44)*".$ent_244."
		FROM attaques
		WHERE players = ".$id_players[0]);
		$sum_e = mysql_fetch_row($req_e);
	}
 
	//Test
	if(!$sum_s[0]) $sum_s[0]=0;
	if(!$sum_a[0]) $sum_a[0]=0;
	if(!$sum_e[0]) $sum_e[0]=0;
 
	$update_players = mysql_query("UPDATE players SET players.restes = players.restes + ".$sum_s[0]." - ".$sum_a[0]." - ".$sum_e[0]." WHERE players.id = ".$id_players[0]);
}

ça veut pas le faire tout dans la meme je sais pas pk j'y arrive pas

**pcaboche** · 15/01/2006, 20h31

Envoyé par dark_vidor

ben ça me donne ça ... pk renommez le nom des tables ?

Quand tu inclues plusieurs fois la même table (ici 'players'), il faut pouvoir les distinguer. C'est le cas par exemple quand tu fais une jointure réflxive.

Envoyé par dark_vidor

Bon j'ai tester y'a pas d'erreur de syntaxe

Sur une requête aussi longue, ça m'impressionne.

Envoyé par dark_vidor

ça veut pas le faire tout dans la meme je sais pas pk j'y arrive pas

Moi, je testes toujours mes requêtes dans phpMyAdmin (ou un autre outil du genre). Ca me permets de voir ce qui ne va pas et de corriger les erreurs (c'est d'autant plus vrai si la requête est compliquée). Tu devrais en faire autant.

**pcaboche** · 15/01/2006, 20h40

Un autre truc, tu essayes de mettre à jour plusieurs joueurs, pour cela tu fais une boucle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
//Création du tableau des joueurs
$array_players = mysql_query("SELECT id FROM players WHERE team != 3 AND activate = 1");
 
while ($id_players = mysql_fetch_row($array_players))
{
...