Requêtes imbriquées d'exclusion

Version imprimable

13/05/2011, 09h06
BenoitDenis

Requêtes imbriquées d'exclusion

Bonjour à tous

mes souvenirs de SQL étant loin j'ai un peu de mal sur les trucs complexes.

J'ai 2 tables, "produits" et "sortis" qui ont une colonne commune produits.numero et sortis.num_mandat.

Je voudrais connaitre le nombre de lignes qui sont dans ma table produits sans être dans ma table sortis.

Merci d'avance de votre aide.

Version MySql : 5.0.27
Version PhpMyAdmin : 2.8.2.4

bonjour

c'est a dire le nombre de produits qui n'ont jamais ete achetes (sortis) ?
Code:

1 2 3 4 SELECT COUNT(*) FROM Produits LEFT JOIN Sortis ON (Produits.numero = sortis.num_mandat) WHERE sortis.num_mandat IS NULL

13/05/2011, 09h32
BenoitDenis
C'est exactement ça ;)

J'ai réécris je m'étais gouré dans le nom des tables ^^
Code:

1 2 3 4 SELECT COUNT(*) FROM produit LEFT JOIN sortis ON (produit.numero = sortis.num_mandat) WHERE sortis.num_mandat IS NULL
Pour l'instant ma requête tourne dans le vide, bizarre non ?

Pour info j'ai environ 30 000 lignes dans chaque table.
13/05/2011, 09h40
punkoff

bonjour,

Que veux dire tourne dans le vide ?

Avez-vous un index sur numero et num_mendat ?
13/05/2011, 09h47
BenoitDenis

Quand je dit tourne dans le vide c'est que je lance ma requête dans PhpMyAdmin et la ma page charge longtemps avant de m'afficher une page blanche...

Sur le monitoring serveur, Mysql est monté très très haut en charge, jusqu'à 100% du CPU et redescend tres doucement.

Les tables produit et sortis ont toutes deux un identifiant unique. Qui est la colonne numero pour la table produit et la colonne num_mandat pour la table sorti.
13/05/2011, 09h49
punkoff

sont-ils indexés ?

Peut-etre sont-ils des primary key ?
13/05/2011, 09h53
BenoitDenis

Oui ce sont des Primary Key
13/05/2011, 09h57
dehorter olivier

ca veut dire qu'il n'y a pas de jointure entre les 2 tables ? (de colonnes communes)

peut etre bien de donner la structure des 2 tables ;)
13/05/2011, 10h06
pacmann
Salut !

Je sais pas trop comment MySQL optimise... le must serait que tu nous donnes le plan d'exécution ;)

Cela dit j'imagine bien MySQL avoir du mal avec les jointures externes (comment ça je suis médisant ? :P)...

Tu peux peut être tenter :
Code:

1 2 3 4 5 6 SELECT COUNT(*) FROM Produits a WHERE NOT EXISTS (SELECT NULL FROM sortis b WHERE a.numero = b.num_mandat)

Bonjour

que donne cette requête ?

Code:

1
2
3
4
5
6
7
8
 
SELECT COUNT(*)
FROM produit
WHERE NOT EXISTS (
    SELECT *
    FROM sortis 
    WHERE sortis.num_mandat = produit.numero 
)

13/05/2011, 10h25
dehorter olivier

aieeeuuuu et pacmann,
cette phrase me laisse perplexe :

Citation:

Les tables produit et sortis ont toutes deux un identifiant unique. Qui est la colonne numero pour la table produit et la colonne num_mandat pour la table sorti.

BenoitDenis
peux tu preciser la structure des tables :ccool:
13/05/2011, 10h37
BenoitDenis

Oui oui messieurs me tapez pas ^^ j'ai bien lu tous vos messages mais suis un peu débordé avec plusieurs projets de front :mouarf:

Je reviens vers vous dès que j'ai 5 minutes y'a pas urgence ;)

Je vous enverrai un stagiaire que je vais mettre sur l'affaire ;)

Je connais pas par coeur la structure de la base elle nous est fournie par un prestataire externe
13/05/2011, 10h41
Yanika_bzh

Preferez la solution de pacmann.
En effet, vous beneficierez de l'utilisation des indexes, d'une volumétrie moindre que par la jointure externe.

dans votre table sortie, vous devez avoir une colonne faisant la relation avec le produit, c'est avec celle ci que vous devez jouer pour votre requete.

Bon courage
13/05/2011, 11h07
punkoff

Citation:

Envoyé par Yanika_bzh

Preferez la solution de pacmann.
En effet, vous beneficierez de l'utilisation des indexes, d'une volumétrie moindre que par la jointure externe.

Bonjour,

Ceci est spécifique à l'optimiseur (erm ...) de MySql je suppose ?
13/05/2011, 11h14
Yanika_bzh

sur la non utilisation d'index sur des colonnes NULL ??
13/05/2011, 11h18
pacmann

Normalement, on n'a pas de problème d'indexation de NULL, puisque la colonne est sensée être PK :)

Après Punkoff, le seul moyen de savoir ce qui se passe, c'est d'avoir les plans d'exécution pour les différentes solutions ;)
13/05/2011, 11h20
Yanika_bzh

pas avec l'utilisation de la jointure externe...
13/05/2011, 11h30
punkoff

Citation:

Envoyé par Yanika_bzh

pas avec l'utilisation de la jointure externe...

Pouvez-vous developper ?

En particulier avec quel SGBD vous testez ceci.

Car perso sous oracle j'ai 3 plans d'executions identiques en faisant :
- left outer join
- not in
- not exists

Et il me semble que sous db2 c'est pareil (je ne peux pas vérifier pour l'instant)
13/05/2011, 11h37
pacmann

Citation:

Envoyé par Yanika_bzh

pas avec l'utilisation de la jointure externe...

Oui mais de toutes façons, tu as les NULL après exécution de la jointure externe. (En gros, ce n'est pas les NULL qui t'empêchent d'utiliser l'index, c'est le fait que tu aies déjà exécuté la jointure...)

Si tu veux réaliser la jointure externe, tu peux faire :
- Prendre les lignes de la table A
- Chercher si la clef de jointure existe dans l'index
si oui : retourner ligne
si non : retourner null

(Après, je sais pas si MySQL est capable de faire ça...)

Et comme dit Punkoff, dans d'autres SGBD il est avéré que le même plan peut être utilisé en détectant la recherche de null (NESTED LOOP ANTI ou un truc du genre)
13/05/2011, 12h37
Yanika_bzh

Citation:

Envoyé par punkoff

Pouvez-vous developper ?

En particulier avec quel SGBD vous testez ceci.

Car perso sous oracle j'ai 3 plans d'executions identiques en faisant :
- left outer join
- not in
- not exists

Et il me semble que sous db2 c'est pareil (je ne peux pas vérifier pour l'instant)

ASE 12 par exemple.

Il me semble qu'a partir de la V10 d'Oracle, l'optimisateur reecrit le not in en not exists ... a confirmer.

Vous pouvez aussi aller faire un tour ici , ou bien la.