Discussion :

[Sango64]

Bonjour à tous,

Je travaille avec MySql et je n'arrive pas à trouver une optimisation à une requête qui devient beaucoup trop longue quand le nombre de données augmente.

J'ai 3 tables dans ma base de données :
1 - Documents
2 - Mots
3 - DocumentsMots

La table Documents regroupe tous mes documents et possède les champs idDoc et NomDoc.
La table Mots regroupe tous les mots qui servent à identifier mes documents et possède les champs IdMot et NomMot.
La table DocumentsMots fait le lien entre les deux tables précédentes et possède les champs fk_Documents et fk_Mots.
J'aimerai une requête qui répond à cette demande : "Je veux tous les mots des documents qui possèdent les mots X, Y."

Requête utilisée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
select 			
	fk_Mots		
from			
	DocumentsMots		
where			
	fk_Documents in (		
		select	
			fk_Documents
		from	
			DocumentsMots dm,
			Documents d,
			Mots m
		where	
			d.IdDoc = dm.fk_documents and
			dm.fk_Mots = m.IdMot and
			m.NomMot in ('X', 'Y')
		group by	
			fk_Documents
	)		
group by			
	 fk_Mots

J'ai rajouté dans le fichier en pièce jointe un exemple d'utilisation.

Je vous remercie par avance pour vos conseils.

[tatayo]

Bonjour,
Je pense qu'il faut supprimer la sous-requête:

Code SQL :

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

1
2
3
4
5
6
7
 
select m1.*
from mot m1
inner join DocumentsMots dm1 on dm1.fk_Mots = m1.IdMot 
inner join DocumentsMots dm2 on dm2.fk_document = dm1.fk_document
inner join mot m2 on dm2.fk_Mots = m2.IdMot 
where m2.NomMot IN ('X', 'Y')

Ensuite il faut regarder si les colonnes idmot, fk_mot et fk_document sont bien indexées.

Tatayo.

[Sango64]

Bonjour tatayo,

Tout d'abord merci pour la réponse. La requête est bien plus rapide à s'exécuter pour un résultat similaire sur mes tests (100 secondes au lieu de plus de 600 secondes).
Pour information la table Documents possède '361 000' enregistrements, la table DocumentsMots possède '2 165 511' enregistrements et la table Mots possède '100 073' enregistrements.

La requête sort plusieurs fois le même résultat par exemple 'mot1'. Je vais utiliser le mot clé 'distinct' afin de ne plus avoir de doublons au lieu de 'group by' utilisé dans ma requête précédente.

Est il possible au sein du résultat de cette requête d'enlever les mots contenus dans la clause where ?
Par exemple si j'ai comme résultat W, X, Y, Z et dans la clause where X, Y n'avoir en réalité comme résultat que W, Z.

idmot est une clé primaire, fk_mot et fk_document sont des clés étrangères donc tous ces champs possèdent automatiquement un index non ?
PS : la base est en INNODB

[SQLpro]

1) mettre un GROUP BY inutile est stupide. Cela prend du temps pour rien. Relancez votre première requête sans ce GROUP BY
2) mettez effectivement un DISTINC
3) indexez proprement comme ceci :
mot : (NomMot, IdMot)
DocumentsMots : (fk_document, IdMot)
DocumentsMots : (fk_document, fk_Mots)

A +

[pacmann]

Salut,

Si ton modèle est bien fait et que tes tables bien indexées, il te suffirait de faire :

Code :

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

1
2
3
4
5
6
 
SELECT idMots		
FROM	Mots m
WHERE m.NomMot IN ('X', 'Y')
  AND id_mot IN (SELECT fk_mots
                 FROM DocumentsMots)

En effet, il n'est pas sensé exister de (fk_mots, fk_documents) dans la table de liaison qui n'existe pas dans les tables "maîtres", puisque ce sont des FK... mais il peut cependant juste y avoir des mots qui ne sont plus dans aucun document. S'il n'y a pas de fk sur la table de liaison, il faut les mettre, c'est important (meilleur exemple ici).

"Driver" par la table mots est le plus intéressant puisqu'il y a très peu de lignes qui sont exactement X ou Y.

Le "IN" te dispense de GROUP BY et DISTINCT dans la sous-requête : le SGBD recherche s'il trouve une occurence puis s'arrête.

Côté indexation, petites variantes par rapport à SQL_PRO :
- l'index Mots(NomMot) peut être suffisant, pas besoin de le rendre couvrant en y incluant la PK
- Sur la table de liaison, il faut l'index DocumentsMots(fk_Mots,fk_document), puisque ta recherche se fait à partir du mot, et non en full scan sur les autres tables. Au passage, cette indexation est relativement logique pour une table de liaison, ça devrait servir dans d'autres contextes.

[J1]

J'aimerai une requête qui répond à cette demande : "Je veux tous les mots des documents qui possèdent les mots X, Y."

Bonjour,

ton énoncé et ta requête sont contradictoires : souhaites-tu lister tous les mots des documents qui possèdent les mots X ou Y (comme le laisse penser ta requête) ou bien les mots des documents qui possèdent les mots X et Y (comme le laisse penser ton énoncé) ?

[pacmann]

Bonjour,

ton énoncé et ta requête sont contradictoires : souhaites-tu lister tous les mots des documents qui possèdent les mots X ou Y (comme le laisse penser ta requête) ou bien les mots des documents qui possèdent les mots X et Y (comme le laisse penser ton énoncé) ?

Ah ouais, j'ai complètement zappé l'énoncé et juste regardé la requête... bon j'y vais, me planquer au fond d'un trou

[Sango64]

Bonjour à tous,

Merci pour vos réponses.

@SQLpro :
Je ne savais pas que l'on pouvais créer un index sur plusieurs champs d'une même table c'est intéressant merci.
Par contre je ne comprends pas trop la ligne :
- DocumentsMots : (fk_document, IdMot), IdMot ne fait pas partie de la table DocumentsMots ?

@pacmann :
En fait dans la table DocumentsMots il y a bien les deux clés étrangères fk_mots et fk_documents et les deux champs forment la clé primaire de la table.

@J1 :
Je suis désolé si mon premier post n'est pas très clair. Je veux effectivement les mots des documents qui possèdent les mots X et Y.

Ah ouais, j'ai complètement zappé l'énoncé et juste regardé la requête... bon j'y vais, me planquer au fond d'un trou

[J1]

@J1 :
Je suis désolé si mon premier post n'est pas très clair. Je veux effectivement les mots des documents qui possèdent les mots X et Y.

La différence peut sembler anodine, mais elle ne l'est pas. La requête dont tu as besoin va en effet être beaucoup plus complexe que celle que tu utilisais jusqu'à présent. Elle est techniquement réalisable en SQL, mais attends-toi en revanche à ce qu'elle soit très longue à exécuter.

[pacmann]

Prends la requête de Tatayo, elle est très bien (lui a lu l'énoncé )

Tu as besoin de l'index sur DocumentsMots(fk_mots, fk_documents), car ta recherche reste basée sur la présence des mots dans un document, ce qui est, je l'espère, sencé filtrer très fort.

[pacmann]

Pour information la table Documents possède '361 000' enregistrements, la table DocumentsMots possède '2 165 511' enregistrements et la table Mots possède '100 073' enregistrements.

Il faut aussi penser à l'utilisation qu'on en fait.
Si jamais ta recherche retourne beaucoup de documents, extraire tous les mots de tous ces livres ne sera pas vraiment exploitable. S'ils doivent être consultés par un utilisateur, le mieux est peut être de lui présenter dans un premier niveau les documents qui matchent, et ne chercher tous les mots du document que lorsque l'utilisateur clique dessus.

La requête sort plusieurs fois le même résultat par exemple 'mot1'. Je vais utiliser le mot clé 'distinct' afin de ne plus avoir de doublons au lieu de 'group by' utilisé dans ma requête précédente.

Est il possible au sein du résultat de cette requête d'enlever les mots contenus dans la clause where ?
Par exemple si j'ai comme résultat W, X, Y, Z et dans la clause where X, Y n'avoir en réalité comme résultat que W, Z.

Ajoute "and m1.NomMot NOT IN ('X', 'Y')" pour filtrer

idmot est une clé primaire, fk_mot et fk_document sont des clés étrangères donc tous ces champs possèdent automatiquement un index non ?
PS : la base est en INNODB

Pour les clef primaires, oui un index unique est créé automatiquement pour permettre de valider la contrainte... mais ce n'est pas systématique sur les FK (enfin sur Oracle en tous cas).

[J1]

Prends la requête de Tatayo, elle est très bien (lui a lu l'énoncé )

Malheureusement non, la requête de Tatayo souffre du même problème. Elle renverra les mots dont les documents comprennent au moins 'X' ou au moins 'Y'.
Pour lister les mots dont les documents comprennent 'X' et 'Y', il faut mettre en oeuvre une division relationnelle, et c'est une opération assez complexe en SQL.

Voilà une solution possible. J'ai commenté les différentes "étapes" de la requête pour aider Sango64 à la comprendre.
A noter que j'ai constitué (en lignes 8 à 10 de la requête) une table dérivée RECH comprenant les NOMMOT recherchés ('X' et 'Y') mais l'idéal serait de créer physiquement une table comprenant les NOMMOT recherchés.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT DISTINCT M2.NOMMOT -- 7. on peut à présent lister les NOMMOT des documents comprenant l'ensemble des NOMMOT recherchés
FROM DOCUMENTS D
LEFT JOIN (
		SELECT RD.IDDOC
		FROM ( -- 1. on constitue la table dérivée RD, qui représente toutes les combinaisons possibles entre les différents IDDOC existants et les différents NOMMOT recherchés
				SELECT D.IDDOC, RECH.NOMMOT
				FROM DOCUMENTS D, (
						SELECT 'X' AS NOMMOT
						UNION ALL
						SELECT 'Y') RECH) RD
		LEFT JOIN	( -- 2. on constitue la table dérivée DMM, qui représente les combinaisons réelles  entre IDDOC et NOMMOT
				SELECT DM.FK_DOCUMENTS, M.NOMMOT
				FROM DOCUMENTSMOTS DM
				INNER JOIN MOTS M
					ON M.IDMOT=DM.FK_MOTS) DMM
		ON DMM.FK_DOCUMENTS=RD.IDDOC AND DMM.NOMMOT=RD.NOMMOT
		WHERE DMM.FK_DOCUMENTS IS NULL) DI -- -- 3. combinée au LEFT JOIN entre DMM et RD, cette condition permet de lister les documents ne comprenant *pas* tous les NOMMOT recherchés
	ON DI.IDDOC=D.IDDOC
INNER JOIN DOCUMENTSMOTS DM2 -- 5. on réalise une jointure entre D et DM2 afin de récupérer les IDMOT correspondants aux documents comprenant tous les NOMMOT recherchés
	ON DM2.FK_DOCUMENTS=D.IDDOC
INNER JOIN MOTS M2 -- 6. on réalise une jointure entre DM2 et M2 afin de récupérer les NOMMOT correspondants aux IDMOT correspondants aux documents comprenant tous les NOMMOT recherchés
	ON M2.IDMOT=DM2.FK_MOTS
WHERE DI.IDDOC IS NULL; -- 4. combinée au LEFT JOIN entre D et DI, cette condition permet de lister les documents comprenant tous les NOMMOT recherchés

Attention, comme je l'indiquais, ce type de requête est gourmand.

[pacmann]

La division relationnelle peut aussi s'implémenter par analyse GROUP BY/ HAVING:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT dm.fk_documents, m.NomMots
FROM DocumentsMots dm
  JOIN Mots m ON dm.fk_mots = m.id_mots
WHERE fk_mots IN   
    (
  SELECT fk_documents
  FROM DocumentsMots a
    JOIN Mots b on a.fk_mots = b.id_mots
  GROUP BY fk_documents
  HAVING sum(case when b.NomMot = 'X' then 1 else 0 end) >= 1
    AND  sum(case when b.NomMot = 'Y' then 1 else 0 end) >= 1
    )

[J1]

Je pense que tu voulais plutôt écrire...

Code :

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

1
2
3
4
WHERE fk_documents IN   
    (
  SELECT fk_documents

... mais l'idée est en tout cas intéressante, pacmann ! Il faudrait que je la creuse pour voir si elle gère bien tous les cas de figure (la division relationnelle est une vraie plaie de ce côté-là, l'article de SQL Pro l'illustre bien).
Le souci néanmoins de cette méthode, c'est qu'elle ne permet pas d'effectuer la recherche sur une liste de mots stockés dans une table et qu'il faut donc réécrire une partie de la requête si ta recherche porte par exemple sur trois mots au lieu de deux.

[tatayo]

Et ainsi ?

Code SQL :

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

1
2
3
4
5
6
7
 
SELECT distinct m1.NomMot
FROM mot m1
INNER JOIN DocumentsMots dm1 ON dm1.fk_Mots = m1.IdMot 
INNER JOIN DocumentsMots dm2 ON dm2.fk_document = dm1.fk_document
INNER JOIN mot m2 ON dm2.fk_Mots = m2.IdMot and m2.NomMot = 'X'
INNER JOIN mot m3 ON dm2.fk_Mots = m3.IdMot and m3.NomMot = 'Y'

Tatayo.

[J1]

Et ainsi ?

Code SQL :

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

1
2
3
4
5
6
7
 
SELECT distinct m1.NomMot
FROM mot m1
INNER JOIN DocumentsMots dm1 ON dm1.fk_Mots = m1.IdMot 
INNER JOIN DocumentsMots dm2 ON dm2.fk_document = dm1.fk_document
INNER JOIN mot m2 ON dm2.fk_Mots = m2.IdMot and m2.NomMot = 'X'
INNER JOIN mot m3 ON dm2.fk_Mots = m3.IdMot and m3.NomMot = 'Y'

Tatayo.

Tes deux jointures finales font que ta requête ne renverra aucune ligne, Tatayo.

[tatayo]

Heu... Oups (j'hesite entre et du coup):

Code SQL :

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

1
2
3
4
5
6
7
8
 
SELECT DISTINCT m1.NomMot
FROM mot m1
INNER JOIN DocumentsMots dm1 ON dm1.fk_Mots = m1.IdMot 
INNER JOIN DocumentsMots dm2 ON dm2.fk_document = dm1.fk_document
INNER JOIN mot m2 ON dm2.fk_Mots = m2.IdMot AND m2.NomMot = 'X'
INNER JOIN DocumentsMots dm3 ON dm3.fk_document = dm1.fk_document
INNER JOIN mot m3 ON dm3.fk_Mots = m3.IdMot AND m3.NomMot = 'Y'

Tatayo.

[J1]

Fonctionnellement, je pense que ça doit être ok. Et relativement rapide qui plus est.

Mais techniquement, cela nécessite que Sango64 ajoute deux jointures pour chaque mot supplémentaire recherché. Ce qui présente deux inconvénients :
- le développeur n'a pas toujours la possibilité de modifier à la volée le code de sa requête
- les performances, satisfaisantes pour deux mots recherchés, vont rapidement plonger si la recherche de l'utilisateur porte sur plus de mots

Tout dépend donc des contraintes de Sango64.

[Sango64]

Bonjour,

Je ne pensais pas qu'une telle question allait susciter autant de problématiques. Encore une fois je vous remercie pour l'intérêt que vous y apportez.

Il faut aussi penser à l'utilisation qu'on en fait.
Si jamais ta recherche retourne beaucoup de documents, extraire tous les mots de tous ces livres ne sera pas vraiment exploitable. S'ils doivent être consultés par un utilisateur, le mieux est peut être de lui présenter dans un premier niveau les documents qui matchent, et ne chercher tous les mots du document que lorsque l'utilisateur clique dessus.

Tout dépend donc des contraintes de Sango64.

Le résultat de cette requête va me servir à fournir à l'utilisateur les mots restants correspondants à sa recherche.
Exemple :
1 - Je sélectionne le mot 'X', je vais avoir tous les documents associés au mot 'X' et tous les mots restants des documents trouvés, prenons 'A','B','D','M'. La liste 'A', 'B', 'D', 'M' est donnée par la requête de ce sujet.
2 - Je sélectionne en plus le mot 'D', nous avons donc deux mots sélectionnés. Je vais avoir tous les documents associés aux mots 'X' et 'D' et tous les mots restants des documents trouvés, prenons 'A', 'M'.
3 - On peut répéter le processus au maximum 10 fois. Nous n'aurons pas plus de 10 mots.

Et du coup, je me disais hier soir, si j'ai les idMots, il serait plus rapide d'utiliser les idMots plutôt que les NomMots dans la requête ?

Du coup la requête de tatayo pourrait devenir :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT DISTINCT m1.NomMot
FROM mot m1
INNER JOIN DocumentsMots dm1 ON dm1.fk_Mots = m1.IdMot 
INNER JOIN DocumentsMots dm2 ON dm2.fk_document = dm1.fk_document
INNER JOIN mot m2 ON dm2.fk_Mots = m2.IdMot AND m2.IdMot= nb1 (nb1 = idMots de X)
INNER JOIN DocumentsMots dm3 ON dm3.fk_document = dm1.fk_document
INNER JOIN mot m3 ON dm3.fk_Mots = m3.IdMot AND m3.IdMot = nb2 (nb2 = idMots de Y)

- le développeur n'a pas toujours la possibilité de modifier à la volée le code de sa requête

Pas de contrainte là dessus j'ai les pleins pouvoirs sur la requête

- les performances, satisfaisantes pour deux mots recherchés, vont rapidement plonger si la recherche de l'utilisateur porte sur plus de mots

Du coup si nous avons plus de 5 mots par exemple est ce que les performances ne vont pas tomber exponentiellement ?
Sachant que même si le maximum est de 10 mots, en règle générale 3 voir 4 mots est amplement suffisant.

[J1]

Bonjour,

Du coup si nous avons plus de 5 mots par exemple est ce que les performances ne vont pas tomber exponentiellement ?
Sachant que même si le maximum est de 10 mots, en règle générale 3 voir 4 mots est amplement suffisant.

Tu as plusieurs requêtes à ta disposition, le plus simple est que tu testes chacune avec deux mots, puis trois, puis quatre... (si possible en environnement de test pour ne pas mettre à genoux ton serveur de prod').
Comme je te le disais, si tu optes au final pour "ma" requête, le plus intéressant est de stocker en table temporaire les mots que tu recherches. C'est d'ailleurs tout l'intérêt de cette requête, elle fonctionne quel que soit le nombre de mots recherchés.