Requete trop lente [Résolu]

[theOnlyOne]

Bonjour,

j'ai besoin de votre aide.
J'ai besoin d'optimiser un process.
Actuellement, j'ai le process suivant :

Code PHP :

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
$all=mysql_query("
  SELECT cde_num, cde_datecde, cde_ref, clt_nom, clt_num 
  FROM commande as t1, client as t2 
  WHERE t1.cde_statutarrachage <> 'cloture' 
    AND t1.cde_clt = t2.clt_num 
  ORDER BY t1.cde_num ASC 
");
 
while ($a=mysql_fetch_array($all))
{
 
  $cde_num=$a["cde_num"];
  $arrachage_contenu=mysql_query("
    SELECT contenu_arrstatut 
    FROM arrachage_contenu as t1 
    WHERE t1.contenu_arrstatut = 'valide' 
      AND  t1.contenu_arrcde='$cde_num' 
  ");
 
  $programme=mysql_num_rows($arrachage_contenu);
 
  $commandefourn=mysql_query("
    SELECT contenu_cdenum 
    FROM commandefourn_contenu 
    WHERE contenu_cdenum = '$cde_num' 
      AND contenu_statut='valide' 
  ");
 
  $commande = mysql_num_rows($commandefourn);
 
  if ($programme>0 OR $commande>0)
  {
 
    $clt_nom=$a["clt_nom"];
    $clt_num=$a["clt_num"];
    $date=$a["cde_datecde"];
    $ref=$a["cde_ref"];
     // ... J'affiche la liste
 
  }
}

Qq'un a une idée d'optimisation?

[Madfrix]

Bonjour,

pourquoi faire 3 requetes au lieu d'en faire une seule ? De plus une jointure se fait à l'aide de JOIN pas de WHERE

[theOnlyOne]

Parce que je veux filtrer et que lorsque j'essaie une seule requete, j'ai pas le resultat escompté.

Une idée de requete unique?

[Madfrix]

mets nous stp les schémas de tes tables

[theOnlyOne]

arrachage_contenu
Champ Type Null Défaut Commentaires MIME
contenu_arrnum int(6) Non
contenu_numarr varchar(10) Non
contenu_arrcde varchar(6) Non
contenu_arrcdecontenu varchar(10) Non
contenu_arrclt varchar(6) Non
contenu_arrart varchar(6) Non
contenu_arrcond varchar(10) Non
contenu_arrtaille varchar(50) Non
contenu_arrordre varchar(10) Non
contenu_arrdate date Non 0000-00-00
contenu_arrqtite decimal(10,0) Non 0
contenu_arrreel decimal(10,0) Non 0
contenu_arrachat decimal(10,0) Non 0
contenu_arrsite varchar(3) Non
contenu_arruser varchar(3) Non
contenu_arrstatut varchar(10) Non
contenu_arrbl int(10) Non

commandefourn_contenu
Champ Type Null Défaut Commentaires MIME
contenu_num int(10) Non
contenu_cdefournnum varchar(10) Non
contenu_cdenum varchar(10) Non
contenu_cdenumcontenu varchar(10) Non
contenu_date date Non 0000-00-00
contenu_cltnum varchar(10) Non
contenu_datelivr date Non 0000-00-00
contenu_datearr date Non 0000-00-00
contenu_fourn varchar(10) Non
contenu_statut varchar(20) Non
contenu_article varchar(10) Non
contenu_qtite decimal(10,0) Non 0
contenu_qlivr decimal(10,0) Non 0
contenu_pu decimal(10,2) Non 0.00
contenu_info longtext Non
contenu_bl int(10) Non

commande
Champ Type Null Défaut Commentaires MIME
cde_num int(6) Non
cde_clt varchar(6) Non
cde_type varchar(20) Non
cde_datesaisie date Non 0000-00-00
cde_datecde date Non 0000-00-00
cde_ref varchar(100) Non
cde_adressefactu varchar(5) Non
cde_adresselivr varchar(5) Non
cde_user varchar(3) Non
cde_semaine varchar(2) Non
cde_infolivraison longtext Non
cde_suivis longtext Non
cde_marquage char(1) Non
cde_statutarrachage varchar(30) Non
cde_statutlivr varchar(30) Non
cde_statutfact varchar(30) Non
cde_statut varchar(30) Non


client
Champ Type Null Défaut Commentaires MIME
clt_num int(4) Non
clt_nom varchar(50) Non
clt_adresse1 varchar(50) Non
clt_adresse2 varchar(50) Non
clt_cp varchar(5) Non 0
clt_ville varchar(50) Non
clt_pays varchar(10) Non
clt_tel varchar(15) Non 0
clt_fax varchar(15) Non 0
clt_juridique char(2) Non
clt_siret varchar(13) Non 0
clt_categorie varchar(10) Non
clt_reglt char(2) Non
clt_modereglt varchar(10) Non
clt_domiciliation varchar(50) Non
clt_bque varchar(5) Non
clt_guichet varchar(5) Non
clt_cpte varchar(11) Non
clt_cle char(2) Non
clt_auto decimal(10,2) Non 0.00
clt_encrs decimal(10,2) Non 0.00
clt_remise decimal(10,2) Non 0.00
clt_tva char(3) Non
clt_bl char(1) Non
clt_obs longtext Non
clt_ex decimal(4,0) Non 0
actif char(3) Non

[Madfrix]

Code php :

1
2
3
4
 
...
 AND t1.cde_clt = t2.clt_num 
...

Tu essaies ici de jointer un champs de type varchar(6) avec un champ de type int(4) ? Pas très cohérent ni rapide pour peu que les valeurs matchent...

Règle d'or si applicable : jointer des colonnes numériques entre elles, cela est bien plus rapide

Ceci étant dit, rien ne t'empeche de récupérer un jeu de résultat sur tes tables imbriquées dans ton while puis de filtrer/mettre en forme par php ce sera plus rapide que de faire X requêtes nécessitant une surcharge serveur et des allers retours

[theOnlyOne]

Envoyé par Madfrix

Code php :

Tu essaies ici de jointer un champs de type varchar(6) avec un champ de type int(4) ? Pas très cohérent ni rapide pour peu que les valeurs matchent...

Règle d'or si applicable : jointer des colonnes numériques entre elles, cela est bien plus rapide

Ceci étant dit, rien ne t'empeche de récupérer un jeu de résultat sur tes tables imbriquées dans ton while puis de filtrer/mettre en forme par php ce sera plus rapide que de faire X requêtes nécessitant une surcharge serveur et des allers retours

Je suis d'accord. Je ralentis le serveur avec ces requetes. Mais que voulez vous dire par "rien ne t'empeche de récupérer un jeu de résultat sur tes tables imbriquées dans ton while puis de filtrer/mettre en forme par php"

[CinePhil]

Si je comprends bien ton code, cette requête devrait te donner directement le résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT t1.cde_num, t1.cde_datecde, t1.cde_ref, 
    t2.cltnom, t2.clt_num,
    COUNT(t3.contenu_arrnum) AS nb_arrachage_contenu,
    COUNT(t4.contenu_cdenum) AS nb_commandefourn
FROM commande AS t1
INNER JOIN client AS t2 ON t1.cde_clt = t2.clt_num
INNER JOIN arrachage_contenu AS t3 ON t3.contenu_cdenum = t1.cde_num
INNER JOIN commandefourn_contenu AS t4 ON t4.contenu_cdenum = t1.cde_num
WHERE t1.cde_statutarrachage <> 'cloture'
GROUP BY t1.cde_num, t1.cde_datecde, t1.cde_ref, t2.cltnom, t2.clt_num
ORDER BY t1.cde_num

S'il peut y avoir des commandes sans arrachage_contenu et/ou sans commandefourn, remplace les INNER JOIN par des LEFT OUTER JOIN.

Et profites-en pour apprendre la syntaxe normalisée depuis 1992 pour les jointures !

[theOnlyOne]

Merci pour ta requete, mais le process dure encore + longtemps..

[Madfrix]

La requête que t'a donné CinePhil te permet de ramener les résultats une bonne fois pour toutes sans devoir après effectuer de multiples autres requêtes dynamiquement. C'est donc une requête un peu plus lente, mais en contrepartie, tu ne fais plus d'autres requêtes. Donc une question : as tu supprimé les requêtes dans ton while avant de dire que le process dure plus longtemps ?

[CinePhil]

Autre question classique quand on est confronté à une requête lente : tes tables sont-elles correctement indexées ? Notamment les colonnes figurant dans les jointures (clés étrangères en principe).

[theOnlyOne]

J'ai directement testé la requete sur mysql sans l'intégrer dans le script

[Madfrix]

Quand tu dis lente, c'est combien de temps précisément et sur combien de lignes ? Il faut savoir aussi qu'en modifiant le type du champs de varchar à int pour une reprise d'un projet un jour, j'ai gagne 90% de temps d'exécution de la requête...donc tu sais ce qu'il te reste à faire

[theOnlyOne]

J'ai crée les bons index, modifié mes tables en transformant les varchar en int
Sur 19000 enregistrement, qui doivent extraire 90 enregistrements cela prends 60s...

[CinePhil]

19 000 lignes ce n'est rien du tout pour un SGBD ! Sur un serveur digne de ce nom, cette requête devrait durer moins d'une seconde.

Mais je me rends compte que je me suis trompé dans la deuxième jointure !
Remplace par :

Code :

INNER JOIN arrachage_contenu AS t3 ON t3.contenu_arrcde = t1.cde_num

Ceci étant, comme l'a dit Madfrix, comparer des VARCHAR et des entiers, c'est pour le moins étrange et surtout contre-productif !

On peut avoir le résultat de EXPLAIN suivi de la requête ?

[theOnlyOne]

Résultat de la requête SQL
Serveur: localhost
Base de données: soupe
Généré le : Jeudi 25 Novembre 2010 à 15:05
Généré par: phpMyAdmin 2.6.1 / MySQL 4.1.9-max
requête SQL:

Code :

EXPLAIN SELECT t1.cde_num, t1.cde_datecde, t1.cde_ref, t2.clt_nom, t2.clt_num, COUNT(t3.contenu_arrnum) AS nb_arrachage_contenu, COUNT(t4.contenu_cdenum) AS nb_commandefourn FROM commande AS t1 INNER JOIN client AS t2 ON t1.cde_clt = t2.clt_num LEFT OUTER JOIN arrachage_contenu AS t3 ON t3.contenu_arrcde = t1.cde_num LEFT OUTER JOIN commandefourn_contenu AS t4 ON t4.contenu_cdenum = t1.cde_num WHERE t1.cde_statutarrachage <> 'cloture' GROUP BY t1.cde_num, t1.cde_datecde, t1.cde_ref, t2.clt_nom, t2.clt_num ORDER BY t1.cde_num;

Enregistrements: 4

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL cde_statutarrachage NULL NULL NULL 2576 Using where; Using temporary; Using filesort
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 soupe.t1.cde_clt 1 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 13273
1 SIMPLE t4 ALL NULL NULL NULL NULL 1338

[CinePhil]

On voit qu'il n'utilise quasiment aucune clé (aucun index), notamment sur les tables t3 et t4.

Donne nous les scripts exacts de création des tables :

Code :

SHOW CREATE TABLE nom_de_la_table

[theOnlyOne]

requête SQL: SHOW CREATE TABLE arrachage_contenu;
Enregistrements: 1
Table Create Table
arrachage_contenu

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `arrachage_contenu` (
 `contenu_arrnum` int(6) UNSIGNED NOT NULL AUTO_INCREMENT,
 `contenu_numarr` varchar(10) NOT NULL DEFAULT '',
 `contenu_arrcde` varchar(6) NOT NULL DEFAULT '',
 `contenu_arrcdecontenu` varchar(10) NOT NULL DEFAULT '',
 `contenu_arrclt` varchar(6) NOT NULL DEFAULT '',
 `contenu_arrart` varchar(6) NOT NULL DEFAULT '',
 `contenu_arrcond` varchar(10) NOT NULL DEFAULT '',
 `contenu_arrtaille` varchar(50) NOT NULL DEFAULT '',
 `contenu_arrordre` varchar(10) NOT NULL DEFAULT '',
 `contenu_arrdate` date NOT NULL DEFAULT '0000-00-00',
 `contenu_arrqtite` decimal(10,0) NOT NULL DEFAULT '0',
 `contenu_arrreel` decimal(10,0) NOT NULL DEFAULT '0',
 `contenu_arrachat` decimal(10,0) NOT NULL DEFAULT '0',
 `contenu_arrsite` varchar(3) NOT NULL DEFAULT '',
 `contenu_arruser` varchar(3) NOT NULL DEFAULT '',
 `contenu_arrstatut` varchar(10) NOT NULL DEFAULT '',
 `contenu_arrbl` int(10) NOT NULL,
 PRIMARY KEY (`contenu_arrnum`)
) ENGINE=MyISAM AUTO_INCREMENT=11163 DEFAULT CHARSET=latin1

requête SQL: SHOW CREATE TABLE commandefourn_contenu;
Enregistrements: 1
Table Create Table
commandefourn_contenu

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `commandefourn_contenu` (
 `contenu_num` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `contenu_cdefournnum` varchar(10) NOT NULL DEFAULT '',
 `contenu_cdenum` varchar(10) NOT NULL DEFAULT '',
 `contenu_cdenumcontenu` varchar(10) NOT NULL DEFAULT '',
 `contenu_date` date NOT NULL DEFAULT '0000-00-00',
 `contenu_cltnum` varchar(10) NOT NULL DEFAULT '',
 `contenu_datelivr` date NOT NULL DEFAULT '0000-00-00',
 `contenu_datearr` date NOT NULL DEFAULT '0000-00-00',
 `contenu_fourn` varchar(10) NOT NULL DEFAULT '',
 `contenu_statut` varchar(20) NOT NULL DEFAULT '',
 `contenu_article` varchar(10) NOT NULL DEFAULT '',
 `contenu_qtite` decimal(10,0) NOT NULL DEFAULT '0',
 `contenu_qlivr` decimal(10,0) NOT NULL DEFAULT '0',
 `contenu_pu` decimal(10,2) NOT NULL DEFAULT '0.00',
 `contenu_info` longtext NOT NULL,
 `contenu_bl` int(10) NOT NULL,
 PRIMARY KEY (`contenu_num`)
) ENGINE=MyISAM AUTO_INCREMENT=1219 DEFAULT CHARSET=latin1

requête SQL: show create table commande;
Enregistrements: 1
Table Create Table
commande

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `commande` (
 `cde_num` int(6) UNSIGNED NOT NULL AUTO_INCREMENT,
 `cde_clt` varchar(6) NOT NULL DEFAULT '',
 `cde_type` varchar(20) NOT NULL DEFAULT '',
 `cde_datesaisie` date NOT NULL DEFAULT '0000-00-00',
 `cde_datecde` date NOT NULL DEFAULT '0000-00-00',
 `cde_ref` varchar(100) NOT NULL DEFAULT '',
 `cde_adressefactu` varchar(5) NOT NULL DEFAULT '',
 `cde_adresselivr` varchar(5) NOT NULL DEFAULT '',
 `cde_user` varchar(3) NOT NULL DEFAULT '',
 `cde_semaine` varchar(2) NOT NULL DEFAULT '',
 `cde_infolivraison` longtext NOT NULL,
 `cde_suivis` longtext NOT NULL,
 `cde_marquage` char(1) NOT NULL DEFAULT '',
 `cde_statutarrachage` varchar(30) NOT NULL DEFAULT '',
 `cde_statutlivr` varchar(30) NOT NULL DEFAULT '',
 `cde_statutfact` varchar(30) NOT NULL DEFAULT '',
 `cde_statut` varchar(30) NOT NULL DEFAULT '',
 PRIMARY KEY (`cde_num`)
) ENGINE=MyISAM AUTO_INCREMENT=1992 DEFAULT CHARSET=latin1 PACK_KEYS=0

[CinePhil]

Je vois que contenu_arrcde est toujours en VARCHAR(6) alors qu'elle fait référence à cde_num qui est, fort justement, un entier.

Si tu avais mis en oeuvre, comme il se devrait dans un SGBDR, les clés étrangères en utilisant le moteur InnoDB, tu te serais aperçu de cette erreur.

Idem pour la colonne contenu_cdenum qui est toujours en VARCHAR(10).

Et je ne vois aucune clé d'index dans tes tables !

Voici un exemple d'une de mes tables très simple :

Code :

1
2
3
4
5
6
CREATE TABLE `tr_region_rgn` (
  `rgn_id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rgn_nom` varchar(100) NOT NULL COMMENT 'Nom de la région',
  PRIMARY KEY (`rgn_id`),
  KEY `idx_rgn_nom` (`rgn_nom`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COMMENT='Table de référence des régions françaises'

Remarque la ligne commençant par KEY.

La non-concordance des types entre les clés étrangères et les clés primaires, ajouté au non indexage des tables, hormis bien sûr la clé primaire qui est automatiquement indexée par le SGBD, sont sans doute la cause de la lenteur de la requête.

[theOnlyOne]

Excuse moi, mais je ne t'ai pas envoyé les bons infos.

J'ai 2 postes : un sous window dans lequel j'ai le serveur de développement, et un sous linux d'où je bosse en même temps, et je fais des tests sur les 2 machines en même temps.

Sur le poste window, j'ai bien fait les modifs.