Discussion :

[sinifer]

Bonjour,


j'ai un gro problème avec cette requete ci dessou qui me fait planté easy php.
mais la question aussi que je me pose et que je doit fair un affichage qui me sortira
les origine, l'effectif et la moyenne de commande, logiquement ma requete marche mais elle est trop lourd je voulai savoir si j'ai pas moyen de l'optimisé

car après 3 minute sa me mets: Fatal error: Maximum execution time of 300 seconds exceeded in C:\Program Files\EasyPHP 3.0\phpmyadmin\libraries\dbi\mysqli.dbi.lib.php on line 164

voici la requete:

Code :

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

1
2
3
4
5
6
7
8
 
SELECT IF(customers_from LIKE '1st%','1rst',customers_from) AS Origin, 
       COUNT(*) AS effectif, AVG(commandes.commandes_montant) AS Mmc 
FROM customers 
inner join commandes on customers.customers_id=commandes.customers_id 
inner join relances_gratuites on customers.customers_id=relances_gratuites.customers_id  
WHERE commandes_status='1' and relances_gratuites_inscription_date>='2009-04-11' and  relances_gratuites_inscription_date<='2009-04-28'  
GROUP BY Origin

Ce qui est lourd est le inner join relances_gratuites on customers.customers_id=relances_gratuites.customers_id

Mais je suis obligé car je veu avoir les renseignements qui ce trouve entre ces deux dates.

Es-ce qu'il y a une autre façon de faire ou y-a-t-il une solution pour allegé la requette??

[Maljuna Kris]

Saluton,
Tu peux déjà remplacer ça

Code :

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

1
2
relances_gratuites_inscription_date >=  '2009-04-11'  
AND  relances_gratuites_inscription_date <='2009-04-28'

par

Code :

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

relances_gratuites_inscription_date BETWEEN '2009-04-11' AND '2009-04-28'

mais je crains que ça ne change pas grand chose.
Y-at'il un index sur la colonne relances_gratuites_inscription_date ?
Ce qui doit pénaliser davantage, de mon point de vue, c'est le GROUP BY Origin qui ne peut s'appuyer sur aucun index, les jointures quant à elles s'opérant sur des id, devraient au contraire bénéficier des index.

[sinifer]

Merci Maljuna Kris

Y-at'il un index sur la colonne relances_gratuites_inscription_date ?

non il n'y a pas d'index sur cette colonne

et pour le GROUP BY Origin je suis obligé car je veut l'affichage qui est par origine. Donc je doit le gardé

[Maljuna Kris]

non il n'y a pas d'index sur cette colonne

Eh bien tu devrais en mettre un.
Quant au GROUP BY Origin, je sais bien que tu en as besoin, (je crois même être à l'origine de cette version de la requête). Mais il n'en demeure pas moins que ça doit copieusement plomber le serveur.

En outre j'ai un gros doute sur le COUNT(*), on est censé compter quoi, au juste, les clients ? les commandes ? les relances ?
Parce que là on compte les lignes sans tenir compte des doublons.

[sinifer]

Effectivement j'ai enlevé le count(*)

la requette quej'utilise actuellement est celle là

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 SELECT IF(cu.customers_from LIKE '1st%','1rst',cu.customers_from) AS Origin, 
       COUNT(DISTINCT cu.customers_id) AS effectif, count(co.commandes_status) AS command,
       AVG(co.commandes_montant) AS Mmc 
FROM customers cu
INNER JOIN commandes co 
ON cu.customers_id=co.customers_id 
INNER JOIN relances_gratuites rg 
ON cu.customers_id=rg.customers_id  
WHERE co.commandes_status='1' 
AND relances_gratuites_inscription_date BETWEEN '2009-04-11' AND '2009-04-28'   
GROUP BY Origin

Sa fait 5minute que je l'ai lancé mais toujours rien a tourne

[Maljuna Kris]

la requette quej'utilise actuellement est celle là

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 SELECT IF(cu.customers_from LIKE '1st%','1rst',cu.customers_from) AS Origin, 
       COUNT(DISTINCT cu.customers_id) AS effectif, count(co.commandes_status) AS command,
       AVG(co.commandes_montant) AS Mmc 
FROM customers cu
INNER JOIN commandes co 
ON cu.customers_id=co.customers_id 
INNER JOIN relances_gratuites rg 
ON cu.customers_id=rg.customers_id  
WHERE co.commandes_status='1' 
AND relances_gratuites_inscription_date BETWEEN '2009-04-11' AND '2009-04-28'   
GROUP BY Origin

On peut appliquer le même raisonnement à COUNT(co.commandes_status) et AVG(co.commandes_montant), s'il peut y avoir plusieurs relances gratuites par commande le comptage et la moyenne seront faussés, et là, pas question de s'en tirer avec un DISTINCT, pour la moyenne en tout cas.

[sinifer]

mon COUNT(DISTINCT cu.customers_id) me permet d'avoir un client car comme je regarde avec commande status un client peu être plusieur fois donc je veu le séparé des autres.

Pour la moyenne elle est correct

[sinifer]

il y a une chose que je comprend pas voilà j'ai deux requete le première est celle que j'ai déjà montré et elle me fait planté quant a l'autre qui est celle là

Code :

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

1
2
3
4
5
6
7
8
SELECT IF(customers_from LIKE '1st%','1rst',customers_from) AS Origin, 
       COUNT(*) AS effectif_vg
FROM customers  inner join relances_gratuites on customers.customers_id=relances_gratuites.customers_id 
WHERE customers_client='0' 
AND customers_emv='0' 
AND customers_seance ='0' 
AND relances_gratuites_inscription_date>='2009-04-12' and  relances_gratuites_inscription_date<='2009-05-03'
GROUP BY Origin

marche très bien et me donne les résultats aprè 3 seconde même pas ai-je fai une faute dans ma jointure dans la première requete que j'ai mis???

[Maljuna Kris]

Tu n'as plus la table commandes en jointure et les conditions du filtre WHERE sont différentes.
Le volume des données traitées est probablement nettement moindre.

[sinifer]

oui mais sa veu dir que ce qui fait ramé c'est ma jointure avec la table commande car alors pourquoi sa marche pas sur trois jointure c'est vrai que la condition du where est différente mais es-du a ma troisième jointure??

[Sivrît]

oui mais sa veu dir que ce qui fait ramé c'est ma jointure avec la table commande car alors pourquoi sa marche pas sur trois jointure c'est vrai que la condition du where est différente mais es-du a ma troisième jointure??

Quand on change quelquechose et que ça ne marche plus, il y a de fortes chances qu'il y ait un lien.
Et pour ces cas là, on a EXPLAIN.

A première vue, je dirais que l'ajout d'une jointure change tout car je soupçonne qu'il y ait beaucoup plus d'enregistrements dans "relances_gratuites" que dans "commandes". De plus, le plan d'exécution qui me semble le plus logique passe par la clef étrangère "commandes.customers_id". Est-elle indéxée ?

Enfin, ce qui me gène le plus, il semble y avoir une erreur de logique dans la requête. Les relations customers-commandes et customers-relances_gratuites sont je suppose des relations 1-n. La requête implique donc deux relations 1-n sans les lier, ce qui réaliste un produit cartésien : Si on a n clients, m commandes et o relances, le jeu de données que manipule la requête va avoir une taille de n*m*o. Ça risque de faire beaucoup, et de ne pas donner ce que l'on attend.

[sinifer]

Bonjour,

merci a toi sivrit

j'ai effectué mon explain et sa me met sa:

Code :

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

1
2
3
4
5
 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	rg 	ALL 	NULL 	NULL 	NULL 	NULL 	337195 	Using where; Using temporary; Using filesort
1 	SIMPLE 	co 	ALL 	NULL 	NULL 	NULL 	NULL 	382065 	Using where; Using join buffer
1 	SIMPLE 	cu 	eq_ref 	PRIMARY 	PRIMARY 	4 	titi.rg.customers_id 	1 	Using where

et effectivement il y a plus d'enregistrement dans la table relances_gratuites et il me semble d'après ce tableau qu'il n'y a juste la table relances_gratuites qui soient pas indexé

par contre quand je fais un explain avec cette requete:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
explain
 SELECT co.customers_id
 
FROM customers cu
INNER JOIN commandes co 
ON cu.customers_id=co.customers_id 
INNER JOIN relances_gratuites rg 
ON cu.customers_id=rg.customers_id  
WHERE co.commandes_status='1' 
AND rg.relances_gratuites_inscription_date>='2009-04-26' AND  rg.relances_gratuites_inscription_date<='2009-04-26'

sa me met:

Code :

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

1
2
3
4
5
 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	rg 	ALL 	NULL 	NULL 	NULL 	NULL 	337195 	Using where
1 	SIMPLE 	co 	ALL 	NULL 	NULL 	NULL 	NULL 	382065 	Using where; Using join buffer
1 	SIMPLE 	cu 	eq_ref 	PRIMARY 	PRIMARY 	4 	titi.rg.customers_id 	1 	Using where; Using index

bon mon problème c'est que j'arrive pas très bien a décortiqué le explain mais je vois que titi.rg.customers_id Using Using index.

pour ce qui te gêne je te montre mes table commen elle sont formé

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
 
Commandes
 
commandes_id
commandes_date
customers_id
commandes_relance
commandes_montant
commandes_type
commandes_status
 
 
Costumers
 
customers_id
customers_nom
customers_prenom
customers_export
customers_from
customers_prospect
customers_client
customers_seance
customers_emv
 
 
relances_gratuites
 
relances_gratuites_id
customers_id
relances_gratuites_inscription_date
relances_gratuites_envoi_date
relances_gratuites_open_mail

je sais que la structure des tables sont pas des plus logique mais elles sont tel quel

si t'arrive à m'éclairé dans ce brouillard ce serait gentil merci

[Sivrît]

Code :

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

1
2
3
4
5
 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	rg 	ALL 	NULL 	NULL 	NULL 	NULL 	337195 	Using where; Using temporary; Using filesort
1 	SIMPLE 	co 	ALL 	NULL 	NULL 	NULL 	NULL 	382065 	Using where; Using join buffer
1 	SIMPLE 	cu 	eq_ref 	PRIMARY 	PRIMARY 	4 	titi.rg.customers_id 	1 	Using where

et effectivement il y a plus d'enregistrement dans la table relances_gratuites
et il me semble d'après ce tableau qu'il n'y a juste la table relances_gratuites qui soient pas indexé

Pour savoir s'il y a des index, il faut en fait regarder "SHOW INDEXES" ou "SHOW CREATE TABLE".

Ici, ce qu'il faut noter, c'est que relances_gratuites et Commandes font dans les 300000 lignes chacune. De plus le type de jointure est ALL. Le trouver est généralement mauvais signe car il indique que l'on va récupérer tout le contenu d'une table. Le trouver 2 fois, hormis cas très spéciaux, annonce un cataclysme. Dans ce cas, on croise toutes commandes avec un status 1 avec toutes les relances de la périodes... donc après les deux premières étapes le jeux de données considéré est de l'ordre de 300000*300000 éléments.

C'est bien ce que je pensais, il ne doit pas y avoir d'index du tout hormis les clefs primaire. Je dirais que les index à définir en priorité sont :
relances_gratuites.customers_id
relances_gratuites.relances_gratuites_inscription_date
Commandes.customers_id

Ensuite, je pense que la requête voulue est plutôt 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
 
SELECT IF(cu.customers_from LIKE '1st%','1rst',cu.customers_from) AS Origin, 
       COUNT(DISTINCT cu.customers_id) AS effectif,
       COUNT(co.commandes_status) AS command,
       AVG(co.commandes_montant) AS Mmc 
FROM customers cu
INNER JOIN commandes co ON cu.customers_id=co.customers_id 
WHERE co.commandes_status='1'
  AND EXISTS(SELECT 1
             FROM relances_gratuites rg
             WHERE rg.relances_gratuites_inscription_date BETWEEN '2009-04-11' AND '2009-04-28'
               AND cu.customers_id=rg.customers_id)
GROUP BY Origin

Peut-être sinon que ceci serait plus efficace :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT IF(cu.customers_from LIKE '1st%','1rst',cu.customers_from) AS Origin, 
       COUNT(DISTINCT cu.customers_id) AS effectif,
       COUNT(co.commandes_status) AS command,
       AVG(co.commandes_montant) AS Mmc 
FROM customers cu
INNER JOIN commandes co ON cu.customers_id=co.customers_id 
WHERE co.commandes_status='1'
  AND cu.customers_id IN (SELECT DISTINCT rg.customers_id
                          FROM relances_gratuites rg
                          WHERE rg.relances_gratuites_inscription_date BETWEEN '2009-04-11' AND '2009-04-28')
GROUP BY Origin

bon mon problème c'est que j'arrive pas très bien a décortiqué le explain mais je vois que titi.rg.customers_id Using Using index.

Il y a pas mal de ressources sur le décryptage d'EXPLAIN (du moins pour les requêtes relativement simples) sur le net. Google devrait permettre de trouver de la littérature.

je sais que la structure des tables sont pas des plus logique mais elles sont tel quel

Pour ce genre de chose il y a "SHOW CREATE TABLE" qui a l'avantage d'être bien complet et permet de facilement recréer les tables pour faire des tests.


Bref, une réécriture et des indexes (avec des tables de 300000 lignes on n'y coupe pas) et ça devrait aller mieux.

[sinifer]

Bonjour,

Merci de ta réponse là je suis entrain de regardé pour reconstruire un index mais le problème c'est que c'est marqué que je doit supprimé puis en recréer un index en une seule opération et la j'ai peur de faire une gaffe

doi-je faire comme ça:

Code :

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

1
2
3
4
USE (nom de ma base);
GO
ALTER INDEX ALL ON relances_gratuites.relances_gratuites_inscription_date
REBUILD WITH (ONLINE = ON);

et fair la même chose avec les autre que tu ma conseillé?????

[Sivrît]

Je ne sais pas trop d'où sort cette syntaxe... SQL Server, Oracle peut-être... En tout cas je doute que ça passe avec MySQL. D'autant plus que l'on veut créer des index, pas les reconstruire, ce qui supposerait de déjà les avoir.

La syntaxe la plus simple me semble être CREATE INDEX. Sinon ALTER TABLE xxx ADD KEY sera peut-être plus aux normes.


Pour éviter les gaffes, le mieux est encore de tester et d'expérimenter sur une autre machine, genre une copie locale de la BDD. Ça permet de triturer à loisir, voir le temps que prend l'ajout des index (sur un serveur de production surchargé ça peut être problématique), la place disque, de voir ce que l'on gagne en performances et éventuellement de tenter d'autres alternatives.