Requêtes imbriquées d'exclusion

[BenoitDenis]

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

[dehorter olivier]

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

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

[punkoff]

bonjour,

Que veux dire tourne dans le vide ?

Avez-vous un index sur numero et num_mendat ?

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

[punkoff]

sont-ils indexés ?

Peut-etre sont-ils des primary key ?

[BenoitDenis]

Oui ce sont des Primary Key

[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

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

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)

[aieeeuuuuu]

Bonjour

que donne cette requête ?

Code SQL :

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

[dehorter olivier]

aieeeuuuu et pacmann,
cette phrase me laisse perplexe :

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

[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

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

[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

[punkoff]

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 ?

[Yanika_bzh]

sur la non utilisation d'index sur des colonnes NULL ??

[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

[Yanika_bzh]

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

[punkoff]

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)

[pacmann]

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)

[Yanika_bzh]

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.