Optimisation de requete SQL

Version imprimable

14/01/2006, 22h29
dark_vidor

Optimisation de requete SQL

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:

- trop dense voir plus bas -

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

Pour la version actuel j'utilisait des boucles et plein de variables/constante php la j'en utilise beaucoup moins :lol: ça devrait etre encore mieux ?

Quelqu'un peut il m'aider :P Merci par avance :wink:
14/01/2006, 23h19
pcaboche

Re: Optimisation de requete SQL

Citation:

Envoyé par dark_vidor

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

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.
14/01/2006, 23h21
dark_vidor

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 :roll:

Citation:

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...

***

Citation:

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:

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.

Citation:

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 !

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:

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.

15/01/2006, 12h06
dark_vidor

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

vous pouvez expliquer :)
15/01/2006, 12h54
pcaboche

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.
15/01/2006, 13h31
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 ?
15/01/2006, 15h24
pcaboche

Citation:

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:

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.

Code:

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 :P

au final ça donne :

Code:

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 :cry:

15/01/2006, 20h31
pcaboche

Citation:

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.

Citation:

Envoyé par dark_vidor

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

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

Citation:

Envoyé par dark_vidor

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

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.
15/01/2006, 20h40
pcaboche
Un autre truc, tu essayes de mettre à jour plusieurs joueurs, pour cela tu fais une boucle :
Code:

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)) { ...
J'essayais de faire ceci en une seule opération grâce à l'UPDATE (celui avec 1 requête imbriquée). Dans ce cas, on remplacerait ceci:

Code:

WHERE P1.id = " .$id_players[0]

Par cela:

Code:

WHERE P1.team != 3 AND P1.activate = 1

Et dans ce cas, plus besoin de boucles, moins de requêtes SQL, le réseau est moins chargé, et donc ça va plus vite !