optimisation table ou requete ?

**p_m_g** · 25/02/2011, 10h20

Bonjour,

voici mon souci :
sur ma page web je liste dans un tableau les clients d'une table "clients" (200 Ko. pas de PB jusque là.
je voudrais ensuite ajouter une colonne avec "total transactions" qui correspond à la nombre de transactions effectuées par client.

donc dans ma boucle qui liste les clients j'effectue ce calcul (nombre id de chaque client) en attaquant la table "paiement" qui fait environ 180000 enregistrements et qui pèse environ 32 Mo.

le problème que je constate c'est que je vois un certains ralentissement à l'affichage de la page depuis (j'affiche 30 clients par page pour limiter).

je voudrais aussi faire plus de stats avec ces données mais je pense qu'il faudrait optimiser tout ça avant.

que me conseillez vous ?
diviser la table paiement ? modifier une requête ?

Merci pour l'aide.

**CinePhil** · 25/02/2011, 10h58

Il est là ton ralentissement :

Envoyé par p_m_g

donc dans ma boucle qui liste les clients j'effectue ce calcul (nombre id de chaque client) en attaquant la table "paiement" qui fait environ 180000 enregistrements et qui pèse environ 32 Mo.

En une seule requête avec une fonction de calcul (SUM ou COUNT selon le besoin) et un GROUP BY, tu ramène chaque client avec le calcul fait par le SGBD, ce qui va beaucoup plus vite que de le faire dans une boucle de ton programme, et sans erreur !

Il faudrait que tu nous donnes la description des tables, la requête que tu as effectuée et l'explication de ce que tu veux compter pour qu'on puisse t'aider davantage à construire la requête.

**p_m_g** · 25/02/2011, 11h42

Merci pour ce 1er élément de réponse, je me doutait que faire une requête à chaque fois n'était pas très bon.

donc voici la requête pour lister les clients.
je fais une jointure sur une autre table "users" pour récupérer certaines infos comme le nom du client par exemple.

Code :

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

1
2
3
4
5
6
7
8
 
$query_site = @mysql_query("SELECT T1.*, DATE_FORMAT(T1.date_ins, '%d/%m/%Y') AS date_insertion, T2.nom
FROM users_paiement1 AS T1
LEFT JOIN users AS T2
ON T2.id = T1.id_user
WHERE ".$WHERE_SITE_PACK." // correspond à la recherche
ORDER BY ".$SELECTION." ".$ORDRE." // correspond au tri
LIMIT ".$debut.",".$limit); // correspond à la fourchette des enregistrements

je fais aussi une requête pour afficher le nombre de clients total (par défaut ou selon recherche) :

Code :

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

1
2
 
$query_nb = @mysql_query("SELECT T1.id FROM users_paiement1 AS T1 LEFT JOIN users AS T2 ON T2.id = T1.id_user WHERE ".$WHERE_SITE_PACK);

voici la requête pour afficher le nombre de transactions que je fais à chaque enregistrement :

Code :

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

1
2
3
 
$nb_transactions = mysql_query("SELECT id FROM paiement WHERE id_user = '".$row->id_user."'");
$nb_transac = mysql_num_rows($nb_transactions);

voici la structure des 2 tables principale :

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
 
CREATE TABLE users_paiement1 (
  id int(5) NOT NULL auto_increment,
  id_user int(6) NOT NULL default '0',
  domaine varchar(80) NOT NULL default '',
  nom varchar(80) NOT NULL default '',
  siret varchar(16) NOT NULL default '0',
  cle_marchand varchar(100) NOT NULL default '',
  siret_marchand varchar(30) NOT NULL default '',
  email varchar(70) NOT NULL default '',
  email1 varchar(70) NOT NULL default '',
  email2 varchar(70) NOT NULL default '',
  associe1 varchar(40) NOT NULL default '',
  associe2 varchar(40) NOT NULL default '',
  associe3 varchar(40) NOT NULL default '',
  associe4 varchar(40) NOT NULL default '',
  associe5 varchar(40) NOT NULL default '',
  adresse varchar(100) NOT NULL default '',
  cp varchar(5) NOT NULL default '',
  ville varchar(80) NOT NULL default '',
  tel varchar(15) NOT NULL default '',
  fax varchar(15) NOT NULL default '',
  constat varchar(15) NOT NULL default '',
  horaires tinytext NOT NULL,
  template char(2) NOT NULL default '',
  options_tpl varchar(30) NOT NULL default '',
  modules varchar(40) NOT NULL default '',
  contenu tinyint(1) NOT NULL default '0',
  type_paiement varchar(15) NOT NULL default '',
  paiement_x char(1) NOT NULL default '',
  url_acces_dos varchar(50) NOT NULL default '',
  ti_competence varchar(40) NOT NULL default '',
  tgi_competence int(4) NOT NULL default '0',
  competence_dpt tinyint(4) NOT NULL default '0',
  cnil_num varchar(20) NOT NULL default '',
  cnil_date varchar(10) NOT NULL default '',
  list_villes varchar(150) NOT NULL default '',
  date_ins datetime NOT NULL default '0000-00-00 00:00:00',
  date_modif datetime NOT NULL default '0000-00-00 00:00:00',
  valid tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY id_user (id_user)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0;
 
CREATE TABLE paiement (
  id int(8) NOT NULL auto_increment,
  id_site int(5) NOT NULL default '0',
  nature int(3) NOT NULL default '0',
  montant float(10,2) NOT NULL default '0.00',
  montant1 float(10,2) NOT NULL default '0.00',
  date_mt1 date NOT NULL default '0000-00-00',
  etat1 varchar(15) NOT NULL default '',
  montant2 float(10,2) NOT NULL default '0.00',
  date_mt2 date NOT NULL default '0000-00-00',
  etat2 varchar(15) NOT NULL default '',
  montant3 float(10,2) NOT NULL default '0.00',
  date_mt3 date NOT NULL default '0000-00-00',
  etat3 varchar(15) NOT NULL default '',
  ref varchar(25) NOT NULL default '',
  ref_dossier varchar(25) NOT NULL default '',
  nom varchar(30) NOT NULL default '',
  prenom varchar(30) NOT NULL default '',
  ville varchar(30) NOT NULL default '',
  email varchar(50) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  commentaires text NOT NULL,
  valid varchar(15) NOT NULL default '',
  affich int(3) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY id_site (id_site),
  KEY ref (ref),
  KEY affich (affich),
  KEY valid (valid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

j'ai aussi commencé à faire des requêtes pour afficher des stats avec le nombre de transactions refusées/valides, ou nombre par jour.
mais c'est une requête à chaque fois et ça ralentit l'affichage de la page.

pour info, je suis sur un serveur dédié.

Merci pour l'aide.

**CinePhil** · 25/02/2011, 13h53

Si je comprends bien, tu veux compter les id de la table paiement pour chaque id_user.
La requête simple pour faire ça est celle-ci :

Code :

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

1
2
3
SELECT id_user, COUNT(*) AS nb
FROM paiement
GROUP BY id_user

Et pour avoir le reste des informations de ta première requête, je pense que tu peux faire ça :

Code :

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

1
2
3
4
5
6
7
SELECT p.id_user, u.nom, 
    DATE_FORMAT(up.date_ins, '%d/%m/%Y') AS date_insertion,
    COUNT(p.*) AS nb_transac
FROM paiement p
INNER JOIN user_paiement up ON up.id_user = p.id_user
    INNER JOIN user u ON u.id = up.id_user
GROUP BY p.id_user, u.nom, up.date_ins

Mais j'ai dit "je pense" car j'ai des doutes sur la normalisation de ton modèle de données, sans toutefois avoir regardé ça en détail.

**p_m_g** · 25/02/2011, 16h16

ça avance doucement mais surement..

j'ai oublié de mettre la table users :

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
 
CREATE TABLE users (
  id int(9) NOT NULL auto_increment,
  societe varchar(80) NOT NULL default '',
  ref_clt varchar(12) NOT NULL default '',
  typ_clt tinyint(2) NOT NULL default '0',
  pack int(1) NOT NULL default '0',
  siret varchar(20) NOT NULL default '',
  adresse varchar(130) NOT NULL default '',
  cp varchar(6) NOT NULL default '',
  ville varchar(100) NOT NULL default '',
  lat decimal(13,10) NOT NULL default '0.0000000000',
  `long` decimal(13,10) NOT NULL default '0.0000000000',
  cle_map varchar(100) NOT NULL default '',
  tel1 varchar(12) NOT NULL default '',
  tel2 varchar(12) NOT NULL default '',
  fax varchar(12) NOT NULL default '',
  email varchar(80) NOT NULL default '',
  email1 varchar(80) NOT NULL default '',
  email_fact varchar(80) NOT NULL default '',
  rep_local varchar(40) NOT NULL default '',
  rep_distant varchar(40) NOT NULL default '',
  site varchar(100) NOT NULL default '',
  niveau int(3) NOT NULL default '0',
  site_web tinyint(1) NOT NULL default '0',
  site_pel int(1) NOT NULL default '0',
  type_pel varchar(15) NOT NULL default '',
  cle_marchand varchar(100) NOT NULL default '',
  siret_marchand varchar(30) NOT NULL default '',
  modules varchar(50) NOT NULL default '',
  passwd varchar(10) NOT NULL default '',
  base varchar(30) NOT NULL default '',
  `user` varchar(30) NOT NULL default '',
  pass varchar(15) NOT NULL default '',
  commentaires text NOT NULL,
  date_ins date NOT NULL default '0000-00-00',
  date_modif date NOT NULL default '0000-00-00',
  date_connect datetime NOT NULL default '0000-00-00 00:00:00',
  date_refresh datetime NOT NULL default '0000-00-00 00:00:00',
  last_page varchar(100) NOT NULL default '',
  nb_connect int(5) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY ref_clt (ref_clt)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

le lien entre les 3 tables est le suivant :
users.id = users_paiement.id_user = paiement.id_site

le but est d'arriver à faire une seule requête, mais je ne suis pas encore là.
pour le moment j'arrive bien au résultat voulu mais en passant par un traitement PHP.

voici le code :

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
 
// selection des données clients avec le pack
$query_site = @mysql_query("SELECT T1.*, DATE_FORMAT(T1.date_ins, '%d/%m/%Y') AS date_insertion, T2.pack
FROM users_paiement1 AS T1
LEFT JOIN users AS T2
ON T2.id = T1.id_user
WHERE ".$WHERE_SITE_PACK."
ORDER BY ".$SELECTION." ".$ORDRE." 
LIMIT ".$debut.",".$limit);
 
// nombre de transactions par clients
$nb_transactions = mysql_query("SELECT id_site, COUNT(*) AS nb FROM paiement GROUP BY id_site");
 
// création d'1 tableau avec l'id du client et le NB de transactions
while($res = mysql_fetch_array($nb_transactions))
{
	$tab_nb_trans[] = array($res['id_site'], $res['nb']);
}
 
// boucle liste des clients
$i = 0;
while($row = @mysql_fetch_object($query_site))
{
        // nb de transactions
	foreach($tab_nb_trans as $key => $val)
	{
		if($row->id_user == $val[0])
		{
			$nb_transac = $val[1];
			break;
		}
		else
			$nb_transac = 0;
	}
....
}

je n'arrive pas à faire ceci sur une seule requête comme vous le proposez (peut être du à un nommage de champ id _client non identique ?) mais déjà le temps d'affichage est diminué.