Discussion :

[LE NEINDRE]

Bonjour à tous,
je suis confronté à un gros problème: La requête suivante prend 100% du CPU et n'aboutit jamais !

Code :

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

1
2
3
4
5
 
SELECT DISTINCT (UNB) from message WHERE ID_MESSAGE IN(
SELECT DISTINCT(ID_MESSAGE) FROM commande WHERE NADDP NOT IN
          (SELECT NADDP FROM compte WHERE PAYS='FR' AND CODE_SUPPLIER='Z')
)

Pouvez vous m'éclairer sur la syntaxe où une erreur dans cette requêtes ?
Le nombre d'enregistrments de mes tables est:
message:4500
commande:20000
compte:1000

Merci pour votre aide !!!!

[Maximil ian]

Bonjour,

Y-a-t'il des index sur ID_MESSAGE, NADDP, PAYS et CODE_SUPPLIER ? Essaie aussi sans les DISTINCT...

[LE NEINDRE]

Alors,

ID_MESSAGE: primary key
NAADP: index
PAYS: index
CODE_SUPPLIER:index

Je vais essayer sans les distinct.
Mais la requête te paraît correcte ??

[ren0]

Bonjour,

peux-tu nous poster un explain de ta requete? qu'on y voit + clair.

[Maximil ian]

Mais la requête te paraît correcte ??

Oui

Et effectivement un explain plan serait intéressant.

[LE NEINDRE]

Je veux bien, sans problème !!
Mais c'est quoi un explain plan (dsl, j'suis novice en SQL).

[ren0]

la commande EXPLAIN te permet d'obtenir le plan d'exécution que l'optimiseur de MySQL décide d'adopter pour exécuter ta requete.

tu fais juste EXPLAIN SELECT .... (ta requete SQL)

pour info : http://dev.mysql.com/doc/mysql/en/explain.html

[ska_root]

Salut,

pour faire un explain tu as juste a ajouter le mot clé "EXPLAIN" devant ta requête, en fait cette commande va analyser comment va être traitée la requête...

[LE NEINDRE]

alors après avoir tapé la requete suivante:

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
 
 
EXPLAIN (
 
SELECT DISTINCT (
UNB
)
FROM message
WHERE ID_MESSAGE
IN (
 
SELECT DISTINCT (
ID_MESSAGE
)
FROM commande
WHERE NADDP NOT 
IN (
 
SELECT NADDP
FROM compte
WHERE PAYS = 'FR'
AND CODE_SUPPLIER = 'Z'
)
)
)

j'obtiens ce résultat:

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 SUBQUERY message ALL NULL NULL NULL NULL 4925 Using where; Using temporary 
2 DEPENDENT SUBQUERY commande ALL NULL NULL NULL NULL 18168 Using where; Using temporary 
3 DEPENDENT SUBQUERY compte index_subquery NADDP NADDP 23 func,const,const 123 Using index; Using where

Quelque chose ne va pas ?

[ren0]

Tes index ne sont pas utilisés correcteement donc ça rame. En plus les sous-select oblige mysql à créer des tables temporaires pour traiter ta req.

et ça, ça ramene pas le même résultat ?

Code :

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

1
2
3
4
5
6
7
8
9
SELECT DISTINCT (UNB)
FROM message msg , commande com
WHERE msg.ID_MESSAGE = com.ID_MESSAGE
AND NOT EXISTS (
SELECT NADDP
FROM compte cpt
WHERE cpt.PAYS = 'FR'
AND cpt.CODE_SUPPLIER = 'Z'
AND cpt.NADDP = com.NADDP)

je sais pas si ça va marcher sous ta version de mysql, mais comme tu as les sous select...

[LE NEINDRE]

J'essaie ta requête. Merci
Ma requete ne ressort aucun résultat, je suis obligé d'arréter mysqld.exe car mon pc plante.
Je regarde avec ta requête.

[LE NEINDRE]

Oula !!!!
Résulatt en 0.330 secondes !! ça marche !!!!!!!!!!!!
Meric beaucoup !!!
Qu'ets ce qui fait que ça marche maintenant ???

[ren0]

Code :

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

1
2
3
4
5
6
7
8
9
EXPLAIN SELECT DISTINCT (UNB)
FROM message msg , commande com
WHERE msg.ID_MESSAGE = com.ID_MESSAGE
AND NOT EXISTS (
SELECT NADDP
FROM compte cpt
WHERE cpt.PAYS = 'FR'
AND cpt.CODE_SUPPLIER = 'Z'
AND cpt.NADDP = com.NADDP)

et on va essayer d'expliquer tout ça...

[LE NEINDRE]

Voici le résultat de la requête:

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 PRIMARY com ALL NULL NULL NULL NULL 18168 Using where; Using temporary 
1 PRIMARY msg eq_ref PRIMARY PRIMARY 4 euredi.com.ID_MESSAGE 1   
2 DEPENDENT SUBQUERY cpt ref NADDP NADDP 23 euredi.com.NADDP,const,const 123 Using where; Using index

@ tout d'suite

[ren0]

Si tu regarde bien la colonne rows dans ton explain, tu peux compter le nombre de lignes impactées dans ta requete.

Dans le premier cas : 4925*18168*23= 2057980200 lignes

Dans le second cas : 18168*4*23=1671456 lignes

donc, beaucoup moins dans le second cas.

Pourquoi ?

dans le premier tu as deux fois ALL dans la colonne type c'est à dire que tu balaye completement les tables en question sans utiliser les index.
En plus les résultats intermédiaires sont stockés dans tes tables temporaires par Mysql pour être confrontés aux sub-select....

dans le second cas tu garde 1 ALL sur commande mais tes jointures suivantes sont de type ref et eq_ref. LEs plus performants, basé sur un index...

Il y a beaucoup à dire sur le sujet, je n'ai pas trop le temps, ce n'est qu'un début d'explication...
D'ailleurs ta requete n'est surement pas parfaitement optimisée...

Plus d'info : http://dev.mysql.com/doc/mysql/fr/explain.html

HTH,

[LE NEINDRE]

Ok, merci beaucoup pour toutes ces explications !!!
Je vois que j'ai beaucoup à apprendre sur SQL.
Je vais m'y mettre ... et tenter de comprendre les cpt pour compte etc ...
Merci encore pour ton aide !!!!!
Et bonne aprem

[Maximil ian]

Ce qui est bizarre c'est que chez moi (MySQL 4.1.11) l'EXPLAIN montre 1 seul ALL, pour les deux dernières étapes le type de jointure est un index_subquery et un unique_subquery.

Quelle est ta version de MySQL ? Pourrais-tu poster un SHOW CREATE TABLE de tes tables ?

[LE NEINDRE]

Alors ma version de mysql est: 4.1.9-max

Le résultat de show vreate table sur les différentes tables utilisées donne:

Table message

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE `message` (
`ID_MESSAGE` int(6) NOT NULL auto_increment,
 `UNB` varchar(30) default NULL,
`UNOA` varchar(30) default NULL,
`DATE` varchar(15) default NULL,
`NOM` varchar(20) default NULL,
`DATECREATION` varchar(20) default NULL,
`HEURECREATION` varchar(6) default NULL,
`TAILLE` int(10) default '0',
PRIMARY KEY  (`ID_MESSAGE`),
KEY `NOM` (`NOM`)
) 
ENGINE=MyISAM DEFAULT CHARSET=latin1

Table commande

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
 
CREATE TABLE `commande` (
`ID_COMMANDE` int(8) NOT NULL auto_increment,
`ID_MESSAGE` int(6) NOT NULL default '0',
`BGM` varchar(50) NOT NULL default '-----',
`DTM137` varchar(15) default NULL,
`NADBY` varchar(30) default NULL,
`NADSE` varchar(30) default NULL,
`NADSU` varchar(30) default NULL,
`NADDP` varchar(30) default NULL,
`NBLIGNES` int(4) default NULL,
`DATE_RECEPTION_DOC` varchar(6) NOT NULL default '',
 `HEURE_RECEPTION_DOC` varchar(6) NOT NULL default '',
PRIMARY KEY  (`ID_COMMANDE`),
KEY `BGM` (`BGM`),
KEY `NADDP` (`NADDP`)
) 
ENGINE=MyISAM DEFAULT CHARSET=latin1

Table compte

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE `compte` (
`ID_COMPTE` int(6) NOT NULL auto_increment,
`NADDP` varchar(20) NOT NULL default '',
`CODE_SUPPLIER` char(1) NOT NULL default '',
`PAYS` char(2) NOT NULL default '',
PRIMARY KEY  (`ID_COMPTE`),
KEY `NADDP` (`NADDP`,`CODE_SUPPLIER`,`PAYS`)
) 
ENGINE=MyISAM DEFAULT CHARSET=latin1

[LE NEINDRE]

Au passage,
j'ai beaucoup de mal à comprendre et utiliser les index...
Où pourrais-je trouver de la documentation clair et simple sur l'index, sa définition et son utilisation dans une requête ....

Est-ce qu'il est possible d'utiliser un index dans une clause

Code :

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

WHERE arg1 IN (tableau)

Merci beaucoup pour vos réponses...

[Maximil ian]

Code :

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

KEY `NADDP` (`NADDP`,`CODE_SUPPLIER`,`PAYS`)

Etrange, cet index triple... Il serait plus logique d'utiliser 3 index séparés

Où pourrais-je trouver de la documentation clair et simple sur l'index, sa définition et son utilisation dans une requête ....

Dans la doc MySQL ou sinon ici : http://sqlpro.developpez.com/cours/s...ge=partie2#L10

Est-ce qu'il est possible d'utiliser un index dans une clause

Code :

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

WHERE arg1 IN (tableau)

Tout à fait, ce n'est qu'une autre façon de dire WHERE arg1 = ... OR arg1 = ... OR ...