[MySQL] Optimisation d'un "not in"

Optimisation d'un "not in" [MySQL] - SQL/NoSQL - Programmation

Marsh Posté le 06-01-2006 à 12:37:35    

Bonjour,
 
J'ai une requête que je trouve très lourde et je voudrais savoir comment je peux l'optimiser.
 
Voici la requête :

Code :
  1. SELECT idmes FROM forum_messages WHERE ids NOT IN ( SELECT ids FROM forum_lu )


 
En gros, la table forum_messages contient tous les posts du forum et la table forum_lu sert à stocker les messages lus.
 
Je voudrais donc obtenir la liste des messages qui n'ont jamais été lus.
 
Existe-t-il un moyen d'obtenir ce résultat autrement qu'avec un NOT IN ?
 
Merci d'avance :jap:


Message édité par nero27 le 06-01-2006 à 12:38:29
Reply

Marsh Posté le 06-01-2006 à 12:37:35   

Reply

Marsh Posté le 06-01-2006 à 13:12:44    

SELECT idmes  
FROM forum_messages  
WHERE NOT EXISTS ( SELECT *  
                              FROM forum_lu
                              WHERE forum_lu.ids = forum_messages.ids)

Reply

Marsh Posté le 06-01-2006 à 13:20:48    

ou

Code :
  1. Select
  2. idmes
  3. from
  4. forum_messages
  5. left outer join forum_lu on (forum_messages.ids = forum_lu.ids)
  6. where
  7. forum_lu.ids is null

Message cité 1 fois
Message édité par anapajari le 06-01-2006 à 13:21:35
Reply

Marsh Posté le 06-01-2006 à 15:09:33    

ouaip, mySql 3.X compliant :D

Reply

Marsh Posté le 06-01-2006 à 15:12:23    

anapajari a écrit :

ou

Code :
  1. Select
  2. idmes
  3. from
  4. forum_messages
  5. left outer join forum_lu on (forum_messages.ids = forum_lu.ids)
  6. where
  7. forum_lu.ids is null



C'est plutôt à ce genre de requête que je pensais, étant donné que le not exists ne doit pas être beaucoup moins lourd que le not in.
 
En tout cas, merci, je vais aller potasser la doc MySQL pour bien comprendre tout ça ;)

Reply

Marsh Posté le 06-01-2006 à 15:27:19    

Le NOT IN et NOT EXISTS ne fonctionnent généralement pas de la même façon en interne pour un SGBD donné, en général, le NOT EXISTS utilise l'index (s'il existe) sur la clé attaquée dans la table de la sous-requête, tandis que le NOT IN fait (souvent) des table scans.

Reply

Marsh Posté le 06-01-2006 à 15:52:23    

Le NOT EXISTS fait une oppération similaire à cette requête, du moins sur les SGBD qui ont un optimiseur correct.
 
Test avec un grand volumes de données :
 
Recherche du nombre de produits qui n'ont pas fait l'objet d'une commande de vente dans la société 2.
 

Code :
  1. -- On commence par regarder ce qu il y a dans la base :
  2. select count(*) from pro
  3. => 23658
  5. select count(*) from pro where codsoc = 2
  6. => 2827
  8. select count(*) from evp
  9. => 410184
  11. select count(*) from evp where codsoc = 2 and achvte = 'V' and typeve = 'CDV'
  12. => 122593
  14. -- Avec le NOT EXISTS :
  15. select count(*)
  16. from pro
  17. where pro.codsoc = 2
  18. and not exists (select null
  19.  from evp
  20.  where evp.codsoc = pro.codsoc
  21.  and evp.achvte = 'V'
  22.  and evp.typeve = 'CVD'
  23.  and evp.codpro = pro.codpro)
  24. => 1039
  25. Temps d éxécution : 0:00:00 (instantané on va dire)
  27. -- Solution de la jointure externe :
  28. select count(*)
  29. from pro left outer join evp on evp.codsoc = pro.codsoc and evp.codpro = pro.codpro and evp.achvte = 'V' and evp.typeve = 'CDV'
  30. where pro.codsoc = 2
  31. and evp.codpro is null
  32. => 1039
  33. Temps d éxécution : 0:00:00 (instantané on va dire)
  35. -- Solution avec le NOT IN :
  36. select count(*)
  37. from pro
  38. where pro.codsoc = 2
  39. and pro.codpro not in (select codpro
  40.  from evp
  41.  where evp.codsoc = pro.codsoc
  42.  and evp.achvte = 'V'
  43.  and evp.typeve = 'CDV')
  44. => 1039
  45. Temps d éxécution : 0:00:00 (instantané on va dire)
  47. lol


Bon, le test n'est pas méga concluant, puisqu'apparement, avec SQL Server 2000, le NOT IN ne pose pas vraiment de problème de performances :heink:
 
Les plans d'éxécution :
 
Not Exists :
http://magicbuzz.multimania.com/files/notexists.PNG
 
Left join :
http://magicbuzz.multimania.com/files/leftjoin.PNG
 
Not In :
http://magicbuzz.multimania.com/files/notin.PNG
 
On voit bien que le LEFT JOIN et le NOT EXISTS font la même oppération, mais pas au même moment, donc les différences de perfs seront dans tous les cas vraiment minimes.
 
Par contre, le NOT IN est une véritable usine à gaz, et malgré les très bonnes performances que j'obtiens... Ben moins on joue avec et mieu c'est ;)
 
A noter que j'ai dans ma base les index suivants (ça explique les bonnes perfs)

Code :
  1. -- Inutilisé, mais le fait qu'il soit clustered accélère évidement l'autre index qui a une structure similaire
  2. CREATE UNIQUE CLUSTERED
  3.   INDEX [PK_EVP] ON [dbo].[EVP] ([CODSOC], [ACHVTE], [TYPEVE], [NUMEVE], [NUMPOS])
  4. WITH
  5.     DROP_EXISTING
  6. ON [PRIMARY]
  8. CREATE
  9.   INDEX [ix_evppro] ON [dbo].[EVP] ([CODSOC], [ACHVTE], [TYPEVE], [CODPRO])
  10. WITH
  11.     DROP_EXISTING
  12. ON [PRIMARY]
  14. -- Et pour la table PRO
  15. CREATE UNIQUE CLUSTERED
  16.   INDEX [pk_pro] ON [dbo].[PRO] ([CODSOC], [CODPRO])
  17. WITH
  18.     DROP_EXISTING
  19. ON [PRIMARY]


 
Par contre, je n'ai pas de FK entre les deux tables (qui aurait certainement encore amélioré les perfs du LEFT JOIN et du NOT EXISTS) car j'ai des produits commandés qui ne sont pas dans la table des produits (faut pas me demander pourquoi, c'est pas de ma faute :o)


Message édité par Arjuna le 06-01-2006 à 15:53:27
Reply

Marsh Posté le 06-01-2006 à 15:55:12    

Au fait, ne me demandez pas pkoi SQL Server utilise "ix_sigfou" sur la table PRO... Parcequ'il ne contient même pas CODPRO :heink: et n'est ni unique, ni clustered :pt1cable:

Reply

Marsh Posté le 06-01-2006 à 15:59:00    

Tiens, au fait, un truc important :
 
Ceux qui sont sous SQL Server du moins, LAISSEZ TOMBER LA SOLUTION DU LEFT JOIN, ELLE EST BIEN PLUS LENTE QUE LE NOT EXISTS !
 
En effet, si je fais l'explain plan sur tout le lot, j'ai :
 
LEFT JOIN : 38,97% de coût
NOT EXISTS : 26,96% de coût
NOT IN : 34,07% de coût
 
DONC : La jointure externe est plus lente que le NOT IN !!!
 
 
Pour conclure : le NOT EXISTS c'est bon, mangeaizan !


Message édité par Arjuna le 06-01-2006 à 16:00:07
Reply

Marsh Posté le 06-01-2006 à 16:32:52    

Vais refaire la même chose avec Oracle (en ajoutant le MINUS aussi)
Sauf que le souci, c'est que je ne peux pas faire d'Explain Plan, j'ai pas les droits nécessaire...
 

Code :
  1. select count(*)
  2. from
  3. (
  4. select codpro from pro where codsoc = 2
  5. minus
  6. select codpro from evp where codsoc = 2 and achvte = 'V' and typeve = 'CDV'
  7. ) tmp


=> 1201 en 500 ms
 

Code :
  1. select count(*)
  2. from pro
  3. where codsoc = 2
  4. and codpro not in (select codpro from evp where codsoc = 2 and achvte = 'V' and typeve = 'CDV')


=> 1201 en 2 minutes 31 secondes
 

Code :
  1. select count(*)
  2. from pro
  3. where codsoc = 2
  4. and not exists (select null from evp where evp.codsoc = pro.codsoc and evp.codpro = pro.codpro and evp.achvte = 'V' and evp.typeve = 'CDV')


=> 1201 en 2 minutes et 28 secondes :o
 

Code :
  1. select count(*)
  2. from pro, evp
  3. where pro.codsoc = 2
  4. and evp.codsoc(+) = pro.codsoc
  5. and evp.codpro(+) = pro.codpro
  6. and evp.achvte(+) = 'V'
  7. and evp.typeve(+) = 'CDV'
  8. and evp.codpro is null


(ma version d'Oracle (8.1.7) ne supporte pas la syntaxe "left outer join" )
=> 1201 en 4 minutes 16 secondes
 
Y'a pas à dire, Oracle c'est vraiment de la merde en branche :o
Certes, y'a un peu de monde qui bosse dessus mais quand même !
Les deux bases ont des volumes similaires (j'ai backup sur mon portable la base Oracle il y a quelques mois avec SQL Server)
Quand je pense que mon portable est plus rapide qu'un bi-xéon boosté :o
Peut-être un problème d'indexes... Pourtant logiquement ceux qui existent devraient être utilisés là...
 
M'enfin quand même... L'outil qui est utilisé avec Oracle fait des locks, c'est pas possible autrement, mes requêtes ont dû être bloquées... Referai le test cette nuit quand y'aura plus personne.
 
En tout cas, le coup du MINUS me laisse perplexe ! Domage qu'on ne puisse pas l'utiliser plus souvent ! (usage limité, et surtout, supporté par presque aucun SGBD...)

Reply

Marsh Posté le 06-01-2006 à 16:32:52   

Reply

Marsh Posté le 06-01-2006 à 22:35:38    

bizzare tes temps de réponse. Oracle est quand mm bcp utilisé auprès des grosses entreprises. C'est dommage que je n'ai pas une base avec autant de lignes, sinon j'aurais fait le test en 10g

Message cité 1 fois
Reply

Marsh Posté le 06-01-2006 à 23:22:49    

moi23372 a écrit :

bizzare tes temps de réponse. Oracle est quand mm bcp utilisé auprès des grosses entreprises. C'est dommage que je n'ai pas une base avec autant de lignes, sinon j'aurais fait le test en 10g


ça n'empeche pas que ce soit de la merde en branches [:el g]
je dirais même que ça m'étonne même pas, c'est toujours la merde qui se vend le mieux [:el g]

Message cité 1 fois

---------------
J'ai un string dans l'array (Paris Hilton)
Reply

Marsh Posté le 07-01-2006 à 16:37:57    

Harkonnen a écrit :

ça n'empeche pas que ce soit de la merde en branches [:el g]
je dirais même que ça m'étonne même pas, c'est toujours la merde qui se vend le mieux [:el g]


 
c'est ton opinion, moi personnellement d'après mon expérience, oracle ma paru de loin plus accessible pour mes besoins que les autres! SQL Server je regrette sa complexité parfois au niveau de certaines fonctionalité, MySQL, ben il y a pas grand chose, et les autres j'ai pas encore eu l'occas, je testerais bien uen fois POSTgreSQL

Reply

Marsh Posté le 07-01-2006 à 19:30:29    

Tu trouves SQL Server plus complèxe que Oracle ??? :pt1cable:
 
C'est le monde à l'envers :D

Reply

Marsh Posté le 08-01-2006 à 10:13:46    

En tt cas la version 2005 oui... de plus, ayant été à la présentation de SQL SERVER 2005 en belgique, les gens de microsoft prétendait avoir inventé XMLDB (alors que ça a été crée par W3C, et que ORACLE l'implémente depuis déjà la version 9iR2)... Niveau fonctionnalité, SQL SERVER c'est pas encore ça...


Message édité par moi23372 le 08-01-2006 à 10:15:08
Reply

Marsh Posté le 09-01-2006 à 21:10:50    

Voici ma requête finale : est-ce que vous pourriez m'aider à l'alléger SVP ?
 

Code :
  1. SELECT DISTINCT (forum_sujets.ids)
  2. FROM forum_sujets, forum_lu, forum_forums
  3. WHERE
  4. forum_sujets.idf=1
  5. AND (
  6.    forum_sujets.ids not in (
  7.        SELECT forum_lu.ids
  8.        FROM forum_lu, forum_messages
  9.        WHERE forum_lu.ids = forum_messages.ids
  10.        AND forum_lu.idm=10
  11.    )
  12.    OR (
  13.        forum_lu.idm=10
  14.        AND forum_lu.ids = forum_sujets.ids
  15.        AND forum_sujets.idf = forum_forums.idf
  16.        AND forum_lu.nouveau=1
  17.    )
  18. )


J'ai pas réussi à la faire avec un "not exists", ça ne voulait pas fonctionner et je n'arrive pas à l'adapter à un "left join" (je n'arrive pas à comprendre le fonctionnement, donc si vous avez un tuto, je suis preneur :)).


Message édité par nero27 le 09-01-2006 à 21:34:28
Reply

Marsh Posté le 09-01-2006 à 23:16:15    

Code :
  1. SELECT DISTINCT (forum_sujets.ids)
  2. FROM forum_sujets fs, forum_lu, forum_forums
  3. WHERE
  4. forum_sujets.idf=1
  5. AND (
  6.    NOT EXISTS (
  7.        SELECT forum_lu.ids
  8.        FROM forum_lu, forum_messages
  9.        WHERE forum_lu.ids = forum_messages.ids
  10.        AND forum_lu.idm=10
  11.        AND forum_lu = fs.ids
  12.    )
  13.    OR (
  14.        forum_lu.idm=10
  15.        AND forum_lu.ids = forum_sujets.ids
  16.        AND forum_sujets.idf = forum_forums.idf
  17.        AND forum_lu.nouveau=1
  18.    )
  19. )


Message cité 1 fois
Reply

Marsh Posté le 10-01-2006 à 09:15:56    

Beegee a écrit :

Code :
  1. SELECT ... ) )



J'ai testé, mais ça ne fonctionne pas :

Code :
  1. SELECT DISTINCT (
  2. forum_sujets.ids
  3. )
  4. FROM forum_sujets, forum_lu, forum_forums
  5. WHERE forum_sujets.idf =1
  6. AND (
  7. NOT
  8. EXISTS (
  10. SELECT forum_lu.ids
  11. FROM forum_lu, forum_messages, forum_sujets
  12. WHERE forum_lu.ids = forum_messages.ids
  13. AND forum_lu.idm =10
  14. AND forum_lu.ids = forum_sujets.ids
  15. )
  16. OR (
  17. forum_lu.idm =10
  18. AND forum_lu.ids = forum_sujets.ids
  19. AND forum_sujets.idf = forum_forums.idf
  20. AND forum_lu.nouveau =1
  21. )
  22. )

Reply

Marsh Posté le 10-01-2006 à 09:23:14    

Essaye plutôt comme ça :
 

Code :
  1. SELECT DISTINCT (forum_sujets.ids)
  2. FROM forum_sujets fs, forum_lu, forum_forums
  3. WHERE
  4. forum_sujets.idf=1
  5. AND (
  6.    NOT EXISTS (
  7.        SELECT forum_lu.ids
  8.        FROM forum_lu, forum_messages
  9.        WHERE forum_lu.ids = forum_messages.ids
  10.        AND forum_lu.idm=10
  11.        AND forum_lu.ids = fs.ids
  12.    )
  13.    OR (
  14.        forum_lu.idm=10
  15.        AND forum_lu.ids = forum_sujets.ids
  16.        AND forum_sujets.idf = forum_forums.idf
  17.        AND forum_lu.nouveau=1
  18.    )
  19. )


 
j'avais oublié de mettre le nom du champ dans le NOT EXISTS.
Aliaser la table forum_sujets avec fs est nécessaire pour pouvoir l'utiliser dans la sous-requête.

Reply

Marsh Posté le 10-01-2006 à 10:14:07    

Je viens de tester ta requête telle quelle :  
#1109 - Unknown table 'forum_sujets' in field list
 
C'est pour ça que j'avais remplacé le "fs." par "forum_sujets." :/
 
EDIT : il fallait remplacer "forum_sujets." dans la requete principale par "fs." et maintenant ça fonctionne ;)
 
Merci beaucoup :jap:


Message édité par nero27 le 10-01-2006 à 10:16:17
Reply

Marsh Posté le 10-01-2006 à 10:40:40    

De rien (ça doit être une limitation de mySql, il me semble que sous Oracle, même si une table est aliasée, si ce n'est pas ambigu, on peut toujours utiliser le nom de la table ...).

Message cité 1 fois
Reply

Marsh Posté le 10-01-2006 à 10:41:19    

Oui, ça m'a surpris aussi :/

Reply

Marsh Posté le 10-01-2006 à 10:41:38    

Bonjour.
 
J'ai trouvé ce thread en cherchant de l'aide et j'ai essayé d'appliquer la solution proposé au début avec un left outer join, mais maintenant ma requète ne me renvoi plus rien alors que les données semblent êtres bonnes. Si vous pouviez juste me dire si vous remarquez qqchose de travers ce serait sympa car je commence légérement à devenir fou  :pt1cable:  
 
 
Cette requète a pour but de retrouver les identifiants de tous les utilisateurs valides avec date_modif > 1an et qui n'apparaissent pas dans la table relances.
 
Je tourne sous MySql 4.0.24 (important, y a encore bcq de trucs qui marchent pas sous cette version).
 
voilà ma requète :
 

Code :
  1. SELECT user.id_user
  2. FROM user
  3. LEFT OUTER JOIN relances ON (relances.id_user = user.id_user)
  4. WHERE valide=1
  5. AND PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(user.date_modif, '%Y%m')) > 12
  6. AND relances.id_user = null


Message édité par mr_saturne le 10-01-2006 à 10:43:48

---------------
The earth is not a cold dead place...
Reply

Marsh Posté le 10-01-2006 à 11:20:31    

Remplace "= null" par "IS NULL" ;)

Reply

Marsh Posté le 10-01-2006 à 11:40:00    

humm... qqun aurait une corde sur lui?  :sweat:  
 
 
je suis dégouté que ce soit si peu de choses mais en tous cas ca marche! merci bcq beegee!  ;)


---------------
The earth is not a cold dead place...
Reply

Marsh Posté le 10-01-2006 à 12:02:30    

Beegee a écrit :

De rien (ça doit être une limitation de mySql, il me semble que sous Oracle, même si une table est aliasée, si ce n'est pas ambigu, on peut toujours utiliser le nom de la table ...).


Nope, Oracle plante aussi. SQL Server par contre, il s'en contre-fou comme de l'an 40

Reply

Marsh Posté le 11-01-2006 à 16:23:24    

Plus simplement, il suffirais d'ajouter une colonne de plus indiquant le nombre deconsultation; que tu mettra a jour à partir d'un count de la table des consultations. Alors ta requette de consultation s'allege puisqu'il suffira de sélectionner les articles qui ont une consultation = à 0.

Reply

Marsh Posté le 21-01-2006 à 09:52:22    

Bonjour, j'ai encore un problème avec une requête qui fonctionnait chez Free, mais qui ne fonctionne plus chez OVH :/
La voici :

Code :
  1. SELECT TYPE , abri, roue
  2. FROM cage
  3. WHERE id_cage =3984 AND id_user =2258 AND id_cage NOT
  4. IN (
  6. SELECT id_cage
  7. FROM cochon
  8. WHERE id_cage =3984 AND mort =0
  9. )
  10. LIMIT 1


 
Voici l'erreur qui m'est retournée :
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id_cage FROM cochon WHERE id_cage =3984 AND mort =0 ) LI
 
Auriez-vous une idée d'où cela peut-il venir ?
Est-ce possible, en passant, d'optimiser cette requête ?
 
Merci d'avance :jap:


Message édité par nero27 le 21-01-2006 à 09:52:29
Reply

Marsh Posté le 21-01-2006 à 11:08:41    

La version de mySql doit être plus ancienne, et ne doit pas permettre les sous-requêtes.
 
Tu peux à la place utiliser une jointure externe :
 

Code :
  1. SELECT cage.type, cage.abri, cage.roue
  2. FROM cage
  3. LEFT JOIN cochon ON cochon.id_cage = cage.id_cage
  4. WHERE cage.id_cage = 3984
  5. AND cage.id_user = 2258
  6. AND cochon.mort =0
  7. AND cochon.id_cage IS NULL
  8. LIMIT 1;

Message cité 1 fois
Reply

Marsh Posté le 21-01-2006 à 12:15:50    

Beegee a écrit :

La version de mySql doit être plus ancienne, et ne doit pas permettre les sous-requêtes.
 
Tu peux à la place utiliser une jointure externe :
 

Code :
  1. SELECT cage.type, cage.abri, cage.roue
  2. FROM cage
  3. LEFT JOIN cochon ON cochon.id_cage = cage.id_cage
  4. WHERE cage.id_cage = 3984
  5. AND cage.id_user = 2258
  6. AND cochon.mort =0
  7. AND cochon.id_cage IS NULL
  8. LIMIT 1;



Merci beaucoup, ça fonctionne :jap:
 
La version de MySQL est la 4.7, c'est bizarre quand même que le "not in" ne fonctionnait pas :/
 
EDIT: au final, ça ne me retourne pas le résultat comme convenu :/
Au lieu de me retourner 1 champ, ça m'en retourne 0.


Message édité par nero27 le 21-01-2006 à 12:20:56
Reply

Marsh Posté le 02-09-2010 à 11:40:40    

c'est MySQL la merde si tu compares à Oracle
Oracle en base de données c'est le top, mais encore faut-il savoir construire ses requêtes petit scarabée
 
quand tu effectues un SELECT multi table (avec des jointures) il faut mettre les tables les plus importantes à la fin du FROM
si TABLE1 est la table principale qu'elle fait des jointures avec TABLE2 et TABLE3 alors fait
 
SELECT t1.champ1, t1.champ2, t2.champ1
FROM TABLE3 T3, TABLE2 T2, TABLE1 T1
WHERE T1.champ1=T2.champ2
and T1.champ1=T3.champ3
 
L'ordre des jointures dans le where n'est pas important, mais pour des requêtes volumineuses mieux vaut bien les structurer pour ne mas s'y perdre

Message cité 1 fois
Reply

Marsh Posté le 02-09-2010 à 14:00:02    

Je crois qu'en 4 ans il a compris ...
Et l'optimisateur de query se charge tres bien d'organiser les tables, peut importe l'ordre dans la query ...

Reply

Marsh Posté le 03-09-2010 à 07:40:45    

...et pour pas s'y perdre il faut éviter cette syntaxe de jointures dans le where qui date de l'avant-guerre, pour commencer.:o


---------------
Can't buy what I want because it's free -
Reply

Marsh Posté le 05-09-2010 à 21:08:49    

gorgorbhey a écrit :


[...]mais encore faut-il savoir construire ses requêtes petit scarabée


 

gorgorbhey a écrit :


SELECT t1.champ1, t1.champ2, t2.champ1
FROM TABLE3 T3, TABLE2 T2, TABLE1 T1
WHERE T1.champ1=T2.champ2
and T1.champ1=T3.champ3


[:nul]


---------------
J'ai un string dans l'array (Paris Hilton)
Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed