Discussion :

[Jasmine80]

Voici ma requête

Code :

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

1
2
3
4
5
SELECT * FROM fungi2 
WHERE  sequence REGEXP 'CACATTG.*GTCGCA' 
AND organism NOT IN (SELECT DISTINCT(organism) FROM fungi2_consensus 
WHERE linking_consensus LIKE 'Alternaria_alternata') 
AND organism NOT LIKE 'Alternaria alternata'

375 entrées (traitement: 0.4401 sec)


Par contre pour (ou seul 'SELECT DISTINCT(organism)' ne change)

Code :

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

1
2
3
4
5
SELECT DISTINCT(organism) FROM fungi2 
WHERE  sequence REGEXP 'CACATTG.*GTCGCA' 
AND organism NOT IN (SELECT DISTINCT(organism) FROM fungi2_consensus 
WHERE linking_consensus LIKE 'Alternaria_alternata') 
AND organism NOT LIKE 'Alternaria alternata'

Cela ne fonctionne plus, MySQL tourne pendant plusieurs minutes et puis affiche un écran blanc.


Peut-être pourrais-je optimiser mes requêtes.
Fungi2_consensus est une sous-tables provenant de Fungi2 et possède la même PK id liées entre elles par une FK.
J'ai également un index sur le champ Fungi2.organism

Au lieu de faire un SELECT DISTINCT(organism) FROM fungi2_consensus WHERE linking_consensus like 'Alternaria_alternata' serait-il préférable de travailler sur la liste complète des id? Mais, cela allongerait la liste du NOT IN. A moins que je ne crée des index dans la table Fungi_consensus sur les champs organism et linking_consensus.

J'ai aussi essayé de remplacer le LIKE de linking_consensus LIKE 'Alternaria_alternata' par un = mais j'obtiens un résultat différent!

Merci pour vos conseilles.

[Antoun]

Hello Jasmine,

Peux-tu nous donner tes CREATE TABLE ?

Sinon, tu peux simplifier ça :

Code :

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

WHERE  sequence REGEXP 'CACATTG.*GTCGCA'

en :

Code :

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

WHERE  sequence LIKE '%CACATTG%GTCGCA%'

ainsi que ça :

Code :

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

1
2
linking_consensus LIKE 'Alternaria_alternata') 
AND organism NOT LIKE 'Alternaria alternata'

en ça :

Code :

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

1
2
linking_consensus = 'Alternaria_alternata') 
AND organism != 'Alternaria alternata'

[ced]

Bonjour,

Première remarque, l'utilisation du LIKE ne sert ici à rien, puisqu'il n'y a pas de caractère de substitution dans la chaîne comparée (pas de %).
Donc, tu peux les remplacer par <> ou = selon le cas. Je ne sais pas si ça jouera sur les perfs, mais ça peut...

Surtout, pour améliorer les performances, tu peux remplacer les sous-requêtes par des jointures à gauche. Et là, c'est sûr que ça doit changer les choses .
Pour la première requête, ça donne quelque chose comme :

Code :

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

1
2
3
4
5
6
SELECT * 
FROM fungi2 f
LEFT JOIN fungi2_consensus fc ON f.organism = fc.organism AND fc.linking_consensus = 'Alternaria_alternata'
WHERE  sequence REGEXP 'CACATTG.*GTCGCA' 
AND organism <> 'Alternaria alternata'
AND fc.organism IS NULL

Je n'ai pas testé, mais ça doit à peu près faire la même chose (en plus vite).

ced

[Jasmine80]

Merci pour vos conseils.

Première remarque, l'utilisation du LIKE ne sert ici à rien, puisqu'il n'y a pas de caractère de substitution dans la chaîne comparée (pas de %).
Donc, tu peux les remplacer par <> ou = selon le cas.

J'ai essayé de remplacer par = mais cela provoque une erreur de syntaxe, ce qui est étrange car je l'ai dégà utilisé auparavant sans problème dans des requêtes plus simples.

Je voudrais récupérer dans ma table fungi2 les séquences reconnues par l'expression régulière mais dont l'organisme n'est ni Alternata alternaria ni un des organismes de fungi2_consensus lié à Alternaria alternata.

Code :

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

1
2
3
4
5
6
SELECT * 
FROM fungi2 f
LEFT JOIN fungi2_consensus fc ON f.organism = fc.organism AND fc.linking_consensus = 'Alternaria_alternata'
WHERE  sequence REGEXP 'ACATTG.*GTCGCA' 
AND organism <> 'Alternaria alternata'
AND fc.organism IS NULL

Si j'ai bien compris, on lie les organismes de f et fc pour lesquels fc.linking_consensus est 'Alternaria alternata'.
Ensuite on recherche les séquences contenant la REGEXP, dont l'organisme n'est pas Alternaria alternata et pour lesquels fc.organism est NULL, ce qui signifie donc qu'il n'a pas été pris en compte dans le JOIN.
... MySQL rame et n'affiche rien. L'organisme n'est pas unique à chaque entrée, je suppose donc que MySQL s'enmelle les pinceaux.

Si je lie sur l'id au lieu de l'organisme

Code :

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

1
2
3
4
5
6
SELECT * 
FROM fungi2 f
LEFT JOIN fungi2_consensus fc ON f.id = fc.id AND fc.linking_consensus = 'Alternaria_alternata'
WHERE  f.sequence LIKE '%ACATTG%GTCGCA%' 
AND f.organism <> 'Alternaria alternata'
AND fc.id IS NULL

cela rame également ... il tourne pendant 10 minutes puis affiche une page toute blanche.

WHERE sequence REGEXP 'CACATTG.*GTCGCA'
WHERE sequence LIKE '%CACATTG%GTCGCA%'

C'est vrai que le LIKE devrait être plus rapide.

[Jasmine80]

Code :

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

1
2
3
4
SELECT * FROM fungi2 
WHERE  sequence REGEXP 'ACATTG.*GTCGCA' 
AND organism NOT IN (SELECT DISTINCT(organism) FROM fungi2_consensus WHERE linking_consensus = 'Alternaria_alternata') 
AND organism NOT LIKE 'Alternaria alternata'

528 entrées (traitement: 2.7611 sec)


Optimisation 1 de la requête

Code :

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

1
2
3
4
SELECT * FROM fungi2 
WHERE  sequence LIKE '%ACATTG%GTCGCA%' 
AND organism NOT IN (SELECT DISTINCT(organism) FROM fungi2_consensus WHERE linking_consensus = 'Alternaria_alternata') 
AND organism NOT LIKE 'Alternaria alternata'

528 entrées (traitement: 0.1863 sec)


Optimisation 2 de la requête

Code :

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

1
2
3
4
5
SELECT * FROM fungi2 f 
LEFT JOIN fungi2_consensus fc ON f.id = fc.id AND fc.linking_consensus = 'Alternaria_alternata'
WHERE  sequence LIKE '%ACATTG%GTCGCA%' 
AND f.organism NOT LIKE 'Alternaria_alternata' 
AND  fc.id IS NULL

528 entrées (0,1210 sec)


Ce qui ne fonctionnait pas, c'était d'essayer d'utiliser != ou <> au lieu de NOT LIKE. Merci pour vos conseils.

[Jasmine80]

Si j'utilise != ou <> dans une requête, MySQL cherche pendant longtemps puis affiche un écran blanc. Par contre, la même requête exécutée via un programme et donc sans passer par l'interface graphique fonctionne sans le moindre problème.

[Antoun]

Quelle est donc cette terrible interface graphique ?

[Jasmine80]

Quelle est donc cette terrible interface graphique ?

J'utilise EasyPHP 1.8. Une requête simple avec =! ou <> fonctionne très bien mais si j'essaie avec la jointure, cela ne va plus.

Et via mon programme, les 2 requêtes suivantes fonctionnent alors que via MySQL cela ne va plus :

Code :

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

1
2
3
4
5
6
SELECT * 
FROM fungi2 f
LEFT JOIN fungi2_consensus fc ON f.id = fc.id AND fc.linking_consensus = 'Alternaria_alternata'
WHERE  f.sequence LIKE '%ACATTG%GTCGCA%' 
AND f.organism != 'Alternaria alternata'
AND fc.id IS NULL

Code :

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

1
2
3
4
5
6
SELECT organism, COUNT(organism) 
FROM fungi2 
WHERE  sequence LIKE '%ACATTG%TCAAGCTTTGCTTGGTGTTG%GTCGCA%' 
AND organism NOT IN (SELECT DISTINCT(organism) FROM fungi2_consensus WHERE linking_consensus = 'Alternaria_alternata') 
AND organism NOT LIKE 'Alternaria alternata' 
GROUP BY organism

Il faut quand même 5 minutes à mon programme pour exécuter et afficher le résultat. Mes tables doivent être trop remplies. Fungi2 possède 44164 entrées et Fungi2_consensus en a 344.

[Antoun]

!= et <> n'y sont pour rien, et tes tables sont très légères. C'est MySQL qui a des performances catastrophiques avec les sous-requêtes (en particulier le IN), d'où le fait qu'il faille quasi-systématiquement les réécrire sous forme de jointures ou de table dérivées.

[Jasmine80]

Qu'est-ce qu'une table dérivée?

J'ai un autre problème. je me suis rendue compte que linking_consensus équivalait à 'Aletrnaria alternata' sans underscore. Avec le LIKE, cela ne changeait rien par contre avec le =, cela modifie la requête. J'ai donc recommencer les 2 requêtes précédentes que je croyais équivalentes mais mes résultats sont différents.

Code :

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

1
2
3
4
5
SELECT * 
FROM fungi2 
WHERE  sequence LIKE '%ACATTG%GTCGCA%' 
AND organism NOT IN (SELECT DISTINCT(organism) FROM fungi2_consensus WHERE linking_consensus = 'Alternaria alternata') 
AND organism NOT LIKE 'Alternaria alternata'

402 entrées

Code :

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

1
2
3
4
5
6
SELECT * 
FROM fungi2 f
LEFT JOIN fungi2_consensus fc ON f.id = fc.id AND fc.linking_consensus = 'Alternaria alternata'
WHERE f.sequence LIKE '%ACATTG%GTCGCA%' 
AND f.organism NOT LIKE 'Alternaria alternata'
AND fc.id IS NULL

408 entrées

Je ne comprends ce qui diffère entre ces requêtes.


Merci beaucoup pour votre aide.

[Antoun]

1/ une "table dérivée" est une sous-requête dans le FROM (que MySQL implémente, avec des performances correctes, par la création d'une table temporaire).

2/ avec LIKE, le _ est le joker pour un caractère... la requête suivante t'illustrera la différence :

Code :

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

1
2
3
4
5
6
7
SELECT organism 
FROM fungi2
WHERE organism LIKE 'Alternaria_alternata'
UNION
SELECT linking_consensus 
FROM fungi2_consensus 
WHERE organism LIKE 'Alternaria_alternata'

[Jasmine80]

Merci pour les explications.

Code :

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

1
2
3
4
5
6
7
SELECT organism 
FROM fungi2
WHERE organism LIKE 'Alternaria_alternata'
UNION
SELECT linking_consensus 
FROM fungi2_consensus 
WHERE organism LIKE 'Alternaria_alternata'

Je n'ai bien qu'un seul organisme => Alternaria alternata


C'est vraiment étrange non? que les 2 requêtes précédentes me donnent des résultats différents.

[Antoun]

Dans ce cas, le LIKE n'y est pour rien. C'est la jointure qui doit produire qq doublons.

[Jasmine80]

Dans ce cas, le LIKE n'y est pour rien. C'est la jointure qui doit produire qq doublons.

Comment serait-il possible que la jointure crée des doublons alors qu'elle se fait sur l'id qui est la clé primaire des 2 tables jointes?

[Antoun]

Je ne connais pas ton modèle de données, donc je ne peux faire que des suggestions. A toi de vérifier !

[Jasmine80]

J'ai trouvé l'explication.

Différence entre les 2 requêtes :

Code :

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

1
2
3
4
5
6
7
Id	organism		
1362	Colletotrichum gloeosporioides
22442	Colletotrichum gloeosporioides
32104	fungal endophyte
35136	fungal endophyte
43560	fungal endophyte
53487	fungal endophyte

les séquences correspondantes reconnaissent bien le motif %ACATTG%GTCGCA%

le fungi2_consensus.linking_consensus de 31333 Colletotrichum gloeosporioides est bien Alternata alternaria par contre 1362 Colletotrichum gloeosporioides et 22442 Colletotrichum gloeosporioides ne sont pas présents dans la table fungi2_consensus. Explication similaire pour fungal endophyte.

Donc voila, Colletotrichum gloeosporioides se retrouve dans le SELECT DISTINCT(organism) FROM fungi2_consensus WHERE linking_consensus = 'Alternaria alternata' et est donc dans la liste des organismes exclus.
La table Fungi2 possède 3 Colletotrichum gloeosporioides alors que fungi2-consensus en possède 5. Lors de la jointure, seuls 3 d'entre eux sont liés ... et les 2 petits ci-dessus restent seuls sans équivalent dans la table fungi2_consensus. Pour l'autre bestiole, fungal endophyte, 4 se retrouvent dans fungi2_consensus et 8 dans fungi2.
... au total, nous retrouvons bien 6 entrées non liées dans la requête avec jointure qui sont donc récupérées alors qu'elles ne le sont pas dans la requête sans jointure.

Merci beaucoup pour ton aide et tes conseils.