Salut,
Bloqué depuis quelques jours sur une tache, je fais appel à toi lecteur émérite et expert SQL.
Tout d'abord un petit diagramme d'entités-relations
Une archive peut comporter des archives fils. Pour chaque mot clé associé à une archive un poids est attribué et ce pour donner de l'importance au mot.
Un exemple du contenu de la base
Archive
id code label father 1 B28 Confidentiel 2 B28.1 Zone nucléaire 1
Association archive - mot clé
id keyword_id archive_id weight 1 1 1 10 2 1 2 20 3 2 2 30 4 3 2 40
Mots clés
id label 1 SECURITE 2 ZONE 3 NUCLEAIRE
La demande
L'objectif ici est de réaliser une recherche sur cette base.
Scénario d'utilisation
L'utilisateur saisit une chaine de caractère composée de mots du langage courant (français) ou de mots spécifiques. En fonction de ces derniers, une liste d'archives lui est retourné.
Volumétrie
La table d'archive est composée de 100 000 tuples, 80 000 pour la table de mots clés et 1 000 000 d'associations entre ces deux entités.
Le raisonnement
La logique
Pour répondre au problème, différents facteurs sont à prendre en compte :
- L'être humain peut se tromper dans l'orthographe d'un mot, l'accorder, le préfixer...
- Le poids d'un mot lui donne de l'importance
- Donner une plus-value aux archives comportent les x mots clés proches de ce que tape l'utilisateur
Pour répondre au premier facteur j'utilise la distance de levenshtein entre le mot tapé par l'utilisateur et le mot clé, mais aussi la distance de levenshtein entre le double métaphone du mot tapé par l'utilisateur et le double métaphone du mot clé.
Pour le second facteur, plus le poids est élevé plus le mot à de l'importance.
D'un point de vue SQL
Ayant tourné très longtemps sur différentes versions de la requête, mon esprit est maintenant embrouillé et je n'arrive plus à prendre de recul.
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
24
25
26
27
28
29 select f.id, f.code, f.label, min(f.dist) as distF, max(f.poid) as poidF from ( select a.id, a.code, a.label, ( ( levenshtein(lower('Sécurité'), lower(k1.label)) + 1 ) + ( levenshtein(lower('Nucléaire'), lower(k2.label)) + 1 ) ) as dist, ( ka1.balance + kd2.balance ) as poid from archive a inner join assoc_kw_archive ka1 on ka1.archive_id = a.id inner join keyword k1 on k1.id = ka1.keywords_id inner join assoc_kw_archive ka2 on ka2.archive_id = a.id inner join keyword k2 on k2.id = ka2.keywords_id where levenshtein(dmetaphone('Sécurité'), dmetaphone(k1.label)) < 2 and levenshtein(dmetaphone('Nucléaire'), dmetaphone(k2.label)) < 2 ) as f group by f.id, f.code, f.label order by distF asc, poidF desc limit 10;
Le problème
Il est simple. En l'état cette requête fonctionne avec un mot clé, en revanche avec X mots clés elle est très longue (+ de 40 secondes). Normal me direz vous, je fais une jointure pour chaque mot donné par l'utilisateur, et sur 1 million d'associations c'est douloureux. Mais je n'arrive pas à trouver une solution où je donne une plus-value à un ensemble de mots clés proches de la chaine tapée par l'utilisateur.
Conclusion
Je fais appel à ta logique, l'exercice étant de trouver les archives en fonction de la chaine de caractères tapée par l'utilisateur.
Exemple :
recherche -> "Sécurité en zaunes nucléaires" (zaunes au lieu de zones, et aux pluriel alors qu'en base c'est au singulier)
résultat -> Archives "D28.1 Zone nucléaire, D28 Confidentiel"
Si t'as une piste, donnes, si t'as une autre solution de recherche (plein-text...) donnes, si t'as des remarques donnes, en fait donnes tout ce que t'as, je suis preneur
Un grand merci pour ton aide par avance.
Partager