optimisation table ou requete ?

Version imprimable

25/02/2011, 10h20
p_m_g

optimisation table ou requete ?

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.
25/02/2011, 10h58
CinePhil

Il est là ton ralentissement :

Citation:

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.

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:

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:

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:

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:

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.

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:

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:

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.

ça avance doucement mais surement..

j'ai oublié de mettre la table users :

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

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

25/02/2011, 22h36
CinePhil

Citation:

Envoyé par p_m_g

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

Un identifiant de site qui est égal à un identifiant d'utilisateur ! 8O

Citation:

le but est d'arriver à faire une seule requête, mais je ne suis pas encore là.

Ben en principe c'est la requête que je t'ai donnée ; l'as-tu essayée ?

Citation:

pour le moment j'arrive bien au résultat voulu mais en passant par un traitement PHP.

Ça on avait compris au premier message ! :roll:

Citation:

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 ?)

Ben il te faut adapter aux noms de tes colonnes (et pas champ ! :roll: )
28/02/2011, 10h21
p_m_g

Citation:

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

Un identifiant de site qui est égal à un identifiant d'utilisateur !

je ne vois pas ce que tu veux dire ?

Citation:

la requête que je t'ai donnée ; l'as-tu essayée ?

oui, biensur que je l'ai testé mais je n'ai pas réussi à la faire marcher, j'ai des messages d'erreur.
c'est aussi pour cela que je suis passé par un traitement php en attendant de trouver la soluce.

la question est de savoir, si je peux faire tout cela sur une seule requête ?

ce que j'ai besoin, c'est d'afficher la liste des clients (par page de n résultats) avec un certains nombre d'infos présents dans la table "users" et "users_paiement1" et le nombre de transactions par clients de la table "paiement" (voir une somme si c'est possible).
j'ai aussi besoin d'indiquer le nombre de résultats trouvés au total (recherche ou pas). pour ça par défaut, je une clause qui passe à chaque fois et dès que j'ai 1 recherche je mets dans la clause le champ de recherche et la valeur demandée.
28/02/2011, 20h13
CinePhil

Citation:

Envoyé par p_m_g

Citation:

Envoyé par CinéPhil

Citation:

Envoyé par p_m_g

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

Un identifiant de site qui est égal à un identifiant d'utilisateur !

je ne vois pas ce que tu veux dire ?

Tu vois mieux colorié en rouge ? :roll:

Citation:

oui, biensur que je l'ai testé mais je n'ai pas réussi à la faire marcher, j'ai des messages d'erreur.

Et ma boule de cristal va afficher automatiquement sur mon écran les messages d'erreur que tu as reçus peut-être ? :roll:

Citation:

ce que j'ai besoin, c'est d'afficher la liste des clients (par page de n résultats) avec un certains nombre d'infos présents dans la table "users" et "users_paiement1" et le nombre de transactions par clients de la table "paiement" (voir une somme si c'est possible).
j'ai aussi besoin d'indiquer le nombre de résultats trouvés au total (recherche ou pas). pour ça par défaut, je une clause qui passe à chaque fois et dès que j'ai 1 recherche je mets dans la clause le champ de recherche et la valeur demandée.

Euh...
Lis la phrase en bleu de ma signature et applique son principe car ton expression de besoin est incompréhensible !

Un petit jeu de données et le résultat attendu aideraient à comprendre peut-être ?

1 pièce(s) jointe(s)

Citation:

Envoyé par p_m_g

Citation:

Envoyé par CinéPhil

Citation:

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

Un identifiant de site qui est égal à un identifiant d'utilisateur !

Citation:

Envoyé par p_m_g
je ne vois pas ce que tu veux dire ?

Tu vois mieux colorié en rouge ?

ui ui, je vois mieux...8O
en fait la table "paiement" est commune à tous les clients.
dans leur back-office, je remonte les transactions de chaque client avec cet id_site (leur id client de la table "users" est passée en session).

Citation:

Envoyé par CinéPhil
Un petit jeu de données et le résultat attendu aideraient à comprendre peut-être ?

je joins une copie d'écran.Pièce jointe 73784
en jaune les éléments à traiter.
le seul élément à récupérer de la table "users" est le champ pack, le reste vient de la table "users_paiement1" et le nombre de transactions de la table "paiement".

j'ai retesté la requête suivante en corrigeant les noms des tables et des champs :
Code:

1 2 3 4 5 6 7 SELECT up.id_user, up.nom, u.pack, DATE_FORMAT(up.date_ins, '%d/%m/%Y') AS date_insertion, COUNT(p.id) AS nb_transac FROM paiement p INNER JOIN users_paiement1 up ON up.id_user = p.id_site INNER JOIN users u ON u.id = up.id_user GROUP BY p.id_site, up.nom, up.date_ins
par contre, cela me retourne 25 résultats au lieu de 32.