optimisation de requête

Version imprimable

Bonjour je voulais savoir comment faire pour optimiser ma requette ou la raccourcir si quelqu'un peu m'éclairer ou me dirigé ce serai gentille

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$total_commandes = "";
	//client payE 
	//adwords
	$query_ad = "SELECT   COUNT(DISTINCT customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='adwords'  ";
	$result_ad = mysql_query($query_ad); 
	//rien
	$query ="SELECT   COUNT(DISTINCT customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from=''   ";
	$result = mysql_query($query);
	//autre
	$query_oth ="SELECT   COUNT(DISTINCT customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from!='adwords' and  customers_from!='' ";
	$result_oth = mysql_query($query_oth);
	//total
	$query_tt = "SELECT   COUNT(DISTINCT customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )";
	$result_tt = mysql_query($query_tt);

je veu sortire plusieur info dans un tableau merci

22/04/2009, 15h08
comode

Code:

SELECT COUNT(DISTINCT customers_id) AS count_adw FROM customers WHERE (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' ) and (customers_from='adwords' OR customers_from='' " OR (customers_from!='adwords' and customers_from!='' "))

Cela dit avec ton 4e cas - et si je dis pas de bétises - les 3 1er cas sont implicites et donc inutiles !
22/04/2009, 15h22
garrath

euh... la il a une seule reponse... Ce que je comprends c'est qu'il en veut 4 quand meme ;-)

Deja de base tu vires les DISTINCT qui a priori ne servent a rien (on est bien d'accord que customers_id est ta cle primaire de ta table).
Ils ne servent a rien, a part a faire que l'optimiseur de requete va essayer de le traiter avant de se rendre compte que c pas la peine.

Ensuite tu peux faire que 3 requetes a la place des 4. Vue que la 4eme est la somme des 3 premieres.

Apres tu dois pouvoir tout mettre dans une seule requete
SELECT t1.res, t2.res, t3.res FROM (SELECT count(customers_id) as res... ta premiere requete) t1, (SELECT count(customers_id) as res... 2nd requete) t2, (SELECT count(customers_id) as res... 3eme requete) t3
22/04/2009, 15h46
sinifer

donc avec sa garrath je peu avoir la somme d'avec adword san adword et autre??
et je met quel clause where

Code:

SELECT t1.res, t2.res, t3.res FROM (SELECT count(customers_id) as resultat1) t1, (SELECT count(customers_id) as res2) t2, (SELECT count(customers_id) as res3) t3

une fois que j'ai mis sa aprè dans mon where laqul serai la plus aproprié???

tu remets tes requetes completes enfait...

Code:

1
2
3
4
 
SELECT t1.count_adw, t2.count_adw, t3.count_adw FROM (SELECT   COUNT(customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='adwords' ) t1,
(SELECT   COUNT(customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='' ) t2,
(SELECT   COUNT(customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' ) ) t3

et ton dernier tu sommes tes 3 resultats

merci c'est gentile j'ai une autre question si je veut un totale avec d'autre donnée d'une autre table et je veut réutilisé un total de cette table avec un otre total de l'autre table comment je fais
j'utilise le t2.count_r ???

si jamais j'ai rajouté un et gardé le total

Code:

1
2
3
4
5
$query_ad = "SELECT t1.count_adw, t2.count_r, t3.count_aff, t4.count_tt FROM (SELECT   COUNT(customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='adwords' ) t1,
(SELECT   COUNT(customers_id) AS count_r FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='' ) t2,
(SELECT   COUNT(customers_id) AS count_aff FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from!='adwords' and  customers_from!='' ) t3,
(SELECT   COUNT(customers_id) AS count_tt FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' ) ) t4 ";
 $result = mysql_query($query_ad);

22/04/2009, 16h32
garrath

de toutes façons ton resultat tu le recuperes dans des variables php apres tu en fais ce que tu veux.

merci a toi garrath

mais il y a pas une façon plus facile pour optimisé cela

Code:

1
2
3
4
5
6
7
8
9
10
11
12
$query_client = "SELECT t1.count_adw, t2.count_r, t3.count_aff, t4.count_tt FROM (SELECT   COUNT(customers_id) AS count_adw FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='adwords' ) t1,
(SELECT   COUNT(customers_id) AS count_r FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from='' ) t2,
(SELECT   COUNT(customers_id) AS count_aff FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' )and customers_from!='adwords' and  customers_from!='' ) t3,
(SELECT   COUNT(customers_id) AS count_tt FROM customers WHERE  (customers_client='1' or customers_emv='1' or customers_seance ='1' or customers_emv='1' ) ) t4 ";
 $result = mysql_query($query_client);
 
 
	$query_vg = "SELECT vg1.count_adw, vg2.count_r, vg3.count_aff, vg4.count_tt FROM (SELECT   COUNT(customers_id) AS count_adw FROM customers WHERE  customers_client='0' and customers_emv='0' and customers_seance ='0' and customers_emv='0' and customers_from='adwords' ) vg1,
(SELECT   COUNT(customers_id) AS count_r FROM customers WHERE  customers_client='0' and customers_emv='0' and customers_seance ='0' and customers_emv='0' and customers_from='' ) vg2,
(SELECT   COUNT(customers_id) AS count_aff FROM customers WHERE  customers_client='0' and customers_emv='0' and customers_seance ='0' and customers_emv='0' and customers_from!='adwords' and  customers_from!='' ) vg3,
(SELECT   COUNT(customers_id) AS count_tt FROM customers WHERE  customers_client='0' and customers_emv='0' and customers_seance ='0' and customers_emv='0'  ) vg4 ";
 $result_vg = mysql_query($query_vg);

24/04/2009, 11h18
sinifer

il faut pas mettre celà dans une boucle???