[MySQL] Optimisation d'un "not in"

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [MySQL] Optimisation d'un "not in"

nero27

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 :

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

Publicité

moi23372

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

anapajari

s/travail/glanding on hfr/gs;

Code :

Select
idmes
from
forum_messages
left outer join forum_lu on (forum_messages.ids = forum_lu.ids)
where
forum_lu.ids is null

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

Beegee

ouaip, mySql 3.X compliant

nero27

anapajari a écrit :

Code :

Select
idmes
from
forum_messages
left outer join forum_lu on (forum_messages.ids = forum_lu.ids)
where
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

Beegee

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.

Arjuna

Aircraft Ident.: F-MBSD

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 :

-- On commence par regarder ce qu il y a dans la base :
select count(*) from pro
=> 23658
select count(*) from pro where codsoc = 2
=> 2827
select count(*) from evp
=> 410184
select count(*) from evp where codsoc = 2 and achvte = 'V' and typeve = 'CDV'
=> 122593
-- Avec le NOT EXISTS :
select count(*)
from pro
where pro.codsoc = 2
and not exists (select null
from evp
where evp.codsoc = pro.codsoc
and evp.achvte = 'V'
and evp.typeve = 'CVD'
and evp.codpro = pro.codpro)
=> 1039
Temps d éxécution : 0:00:00 (instantané on va dire)
-- Solution de la jointure externe :
select count(*)
from pro left outer join evp on evp.codsoc = pro.codsoc and evp.codpro = pro.codpro and evp.achvte = 'V' and evp.typeve = 'CDV'
where pro.codsoc = 2
and evp.codpro is null
=> 1039
Temps d éxécution : 0:00:00 (instantané on va dire)
-- Solution avec le NOT IN :
select count(*)
from pro
where pro.codsoc = 2
and pro.codpro not in (select codpro
from evp
where evp.codsoc = pro.codsoc
and evp.achvte = 'V'
and evp.typeve = 'CDV')
=> 1039
Temps d éxécution : 0:00:00 (instantané on va dire)
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 :

Left join :

Not In :

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 :

-- Inutilisé, mais le fait qu'il soit clustered accélère évidement l'autre index qui a une structure similaire
CREATE UNIQUE CLUSTERED
INDEX [PK_EVP] ON [dbo].[EVP] ([CODSOC], [ACHVTE], [TYPEVE], [NUMEVE], [NUMPOS])
WITH
DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [ix_evppro] ON [dbo].[EVP] ([CODSOC], [ACHVTE], [TYPEVE], [CODPRO])
WITH
DROP_EXISTING
ON [PRIMARY]
-- Et pour la table PRO
CREATE UNIQUE CLUSTERED
INDEX [pk_pro] ON [dbo].[PRO] ([CODSOC], [CODPRO])
WITH
DROP_EXISTING
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 )

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

Arjuna

Aircraft Ident.: F-MBSD

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:

Arjuna

Aircraft Ident.: F-MBSD

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

Arjuna

Aircraft Ident.: F-MBSD

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 :

select count(*)
from
(
select codpro from pro where codsoc = 2
minus
select codpro from evp where codsoc = 2 and achvte = 'V' and typeve = 'CDV'
) tmp

=> 1201 en 500 ms

Code :

select count(*)
from pro
where codsoc = 2
and codpro not in (select codpro from evp where codsoc = 2 and achvte = 'V' and typeve = 'CDV')

=> 1201 en 2 minutes 31 secondes

Code :

select count(*)
from pro
where codsoc = 2
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

Code :

select count(*)
from pro, evp
where pro.codsoc = 2
and evp.codsoc(+) = pro.codsoc
and evp.codpro(+) = pro.codpro
and evp.achvte(+) = 'V'
and evp.typeve(+) = 'CDV'
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
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é
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...)

Publicité

moi23372

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

Harkonnen

Modérateur
Un modo pour les bannir tous

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)

moi23372

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

Arjuna

Aircraft Ident.: F-MBSD

Tu trouves SQL Server plus complèxe que Oracle ??? :pt1cable:

C'est le monde à l'envers

moi23372

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

nero27

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

Code :

SELECT DISTINCT (forum_sujets.ids)
FROM forum_sujets, forum_lu, forum_forums
WHERE
forum_sujets.idf=1
AND (
forum_sujets.ids not in (
SELECT forum_lu.ids
FROM forum_lu, forum_messages
WHERE forum_lu.ids = forum_messages.ids
AND forum_lu.idm=10
)
OR (
forum_lu.idm=10
AND forum_lu.ids = forum_sujets.ids
AND forum_sujets.idf = forum_forums.idf
AND forum_lu.nouveau=1
)
)

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

Beegee

Code :

SELECT DISTINCT (forum_sujets.ids)
FROM forum_sujets fs, forum_lu, forum_forums
WHERE
forum_sujets.idf=1
AND (
NOT EXISTS (
SELECT forum_lu.ids
FROM forum_lu, forum_messages
WHERE forum_lu.ids = forum_messages.ids
AND forum_lu.idm=10
AND forum_lu = fs.ids
)
OR (
forum_lu.idm=10
AND forum_lu.ids = forum_sujets.ids
AND forum_sujets.idf = forum_forums.idf
AND forum_lu.nouveau=1
)
)

Message cité 1 fois

nero27

Beegee a écrit :

Code :

SELECT ... ) )

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

Code :

SELECT DISTINCT (
forum_sujets.ids
)
FROM forum_sujets, forum_lu, forum_forums
WHERE forum_sujets.idf =1
AND (
NOT
EXISTS (
SELECT forum_lu.ids
FROM forum_lu, forum_messages, forum_sujets
WHERE forum_lu.ids = forum_messages.ids
AND forum_lu.idm =10
AND forum_lu.ids = forum_sujets.ids
)
OR (
forum_lu.idm =10
AND forum_lu.ids = forum_sujets.ids
AND forum_sujets.idf = forum_forums.idf
AND forum_lu.nouveau =1
)
)

Beegee

Essaye plutôt comme ça :

Code :

SELECT DISTINCT (forum_sujets.ids)
FROM forum_sujets fs, forum_lu, forum_forums
WHERE
forum_sujets.idf=1
AND (
NOT EXISTS (
SELECT forum_lu.ids
FROM forum_lu, forum_messages
WHERE forum_lu.ids = forum_messages.ids
AND forum_lu.idm=10
AND forum_lu.ids = fs.ids
)
OR (
forum_lu.idm=10
AND forum_lu.ids = forum_sujets.ids
AND forum_sujets.idf = forum_forums.idf
AND forum_lu.nouveau=1
)
)

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.

nero27

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

Beegee

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

nero27

Oui, ça m'a surpris aussi

mr_saturne

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 :

SELECT user.id_user
FROM user
LEFT OUTER JOIN relances ON (relances.id_user = user.id_user)
WHERE valide=1
AND PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(user.date_modif, '%Y%m')) > 12
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...

Beegee

Remplace "= null" par "IS NULL"

mr_saturne

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

Arjuna

Aircraft Ident.: F-MBSD

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

BarraudDen

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.

nero27

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 :

SELECT TYPE , abri, roue
FROM cage
WHERE id_cage =3984 AND id_user =2258 AND id_cage NOT
IN (
SELECT id_cage
FROM cochon
WHERE id_cage =3984 AND mort =0
)
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

Beegee

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 :

SELECT cage.type, cage.abri, cage.roue
FROM cage
LEFT JOIN cochon ON cochon.id_cage = cage.id_cage
WHERE cage.id_cage = 3984
AND cage.id_user = 2258
AND cochon.mort =0
AND cochon.id_cage IS NULL
LIMIT 1;

Message cité 1 fois

nero27

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 :

SELECT cage.type, cage.abri, cage.roue
FROM cage
LEFT JOIN cochon ON cochon.id_cage = cage.id_cage
WHERE cage.id_cage = 3984
AND cage.id_user = 2258
AND cochon.mort =0
AND cochon.id_cage IS NULL
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

gorgorbhey

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

Oliiii

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

skeye

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

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

Harkonnen

Modérateur
Un modo pour les bannir tous

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)

Publicité

FORUM HardWare.fr

Programmation

SQL/NoSQL

[MySQL] Optimisation d'un "not in"

Sujets relatifs
[MySQL] Besoin d'aide - Optimisation d'une requête très lourde	[MySQL] Cherche 3eme dan Optimisation: requette avec SubQuery correlée
[PHP/mySQL] conseils d'optimisation	Pb d'optimisation php/mysql
[MySQL] Optimisation requete sur ENORME table ...	[PHP - MYSQL] optimisation d'une requete
[Php/Mysql/Tableaux] Optimisation d'un code	Optimisation MySQL?
[mysql]optimisation d'un requete	Optimisation mysql
Plus de sujets relatifs à : [MySQL] Optimisation d'un "not in"

Page générée en 0.165 secondes