Indexation d'un champ texte pour accélérer la recherche d'une chaîne de caractères.

[Richard_35]

Bonjour à tous,

Cette méthode d'indexation de champ alpha-numérique (texte, mémo, etc...) est intéressante, en terme de temps de réponse.

Prenons l'exemple d'une table Produit :
- Id_Produit (PK)
- Libellé (PK)
...

Contenu :

Code :

1
2
3
4
5
Id_Produit	Libellé
123ABC		Petits pois
456DEF		Orange d'Espagne
789GHI		Pois chiche
...

Cahier des charges :
Rechercher tous les produits dont le libellé contient une chaîne de caractères saisie, où qu'elle soit dans Libellé.
==> exemple : saisie de la chaîne de caractères "poi".


Solution sans indexation :

Code sql :

SELECT Id_Produit, Libellé FROM Produit WHERE Libellé LIKE "*poi*"

==> nous savons tous (ou nous nous doutons tous) que le LIKE "*poi*" est gourmand surtout à cause de l'étoile du début : en effet, le système est obligé de parcourir l'ensemble de la Table pour repérer les enregistrements dont Libellé contient, à n'importe quel endroit, la chaîne de caractères "poi".


Solution avec indexation :

1. Création d'une table Produit_IndexLibellé :
   - Libellé_Travaillé (PK)
   - Id_Produit (PK)
   
   
   
2. Ecriture d'un objet programme (module, trigger, etc...) qui, à chaque [création] ou [modification du libellé d'un produit], "décompose" ce libellé de manière à utiliser LIKE "xxx*" (et non LIKE "*xxx*").
   
   Par exemple, à la création du produit 456DEF (Orange d'Espagne), cet objet programme devra effectuer les actions suivantes :
   
   Code sql :

   DELETE * FROM Produit_IndexLibellé WHERE Id_Produit="456DEF"

   ==> suppression de l'ancien index sur le libellé, pour le produit concerné : 456DEF.
   
   
   Remplissage de la table Produit_IndexLibellé comme suit :
   
   Code :

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   Libellé_Travaillé	Id_Produit
   orangedespagne		456DEF
   rangedespagne		456DEF
   angedespagne		456DEF
   ngedespagne		456DEF
   gedespagne		456DEF
   edespagne		456DEF
   despagne		456DEF
   espagne			456DEF
   spagne			456DEF
   pagne			456DEF
   agne			456DEF
   gne			456DEF

   ==> 3 caractères étant le nombre de caractères minimum à rechercher.
   ==> attention à :

   • tout convertir en minuscule ;
   • remplacer les caractères accentués par le caractère sans accent correspondant ;
   • ne prendre en compte que les caractères de "a" à "z" et de "1" à "9" (à adapter) ;
   • ne prendre en compte que les caractères de "a" à "z" et de "1" à "9" (à adapter) pour le champ de saisie des caractères à rechercher.

   
   
3. Donc, en final, dans notre exemple, le contenu complet de Produit_IndexLibellé sera :
   
   Code :

   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
   Libellé_Travaillé	Id_Produit
   orangedespagne		456DEF
   rangedespagne		456DEF
   angedespagne		456DEF
   ngedespagne		456DEF
   gedespagne		456DEF
   edespagne		456DEF
   despagne		456DEF
   espagne			456DEF
   spagne			456DEF
   pagne			456DEF
   agne			456DEF
   gne			456DEF
   petitspois		123ABC
   etitspois		123ABC
   titspois		123ABC
   itspois			123ABC
   tspois			123ABC
   spois			123ABC
   pois			123ABC
   ois			123ABC
   poischiche		789GHI
   oischiche		789GHI
   ischiche		789GHI
   schiche			789GHI
   chiche			789GHI
   hiche			789GHI
   iche			789GHI
   che			789GHI

   En recherchant la chaîne de caractères "poi" :
   
   Code sql :

   SELECT * FROM Produit WHERE Libellé LIKE "poi*"

   trouvera :
   
   Code :

   1
   2
   3
   Libellé_Travaillé	Id_Produit
   pois			123ABC
   poischiche		789GHI

   en un temps record... au détriment, bien entendu, de la place disque.

[f-leb]

Salut,

Ça doit être pour ça qu’on a inventé l’indexation plein-texte en SQL

Quitte à bricoler un truc si le SGBD (indigne) ne possède pas d’indexation full-text, je ferais plutôt un truc comme:

Code :

1
2
3
4
5
6
7
MotClef
idMot	Mot
1	"petits"
2	"pois"
3	"orange"
4	"Espagne"
5	"Chiche"

Code :

1
2
3
4
5
6
7
8
Indexer
idMot	idProduit
1	"123ABC"
2	"123ABC"
2	"789GHI"
3	"456DEF"
4	"456DEF"
5	"789GHI"

[Richard_35]

Bonsoir Fabien,

Envoyé par Fabien

.../... l’indexation plein-texte en SQL

==> désolé, je ne connais pas.


Ta méthode est valable, bien entendu, mais tu es obligé d'utiliser le LIKE "*xxx*" pour trouver une chaîne de caractères. Donc, OK, il est plus efficace de chercher une chaîne de caractères dans des mots clés que dans les libellés complets, c'est vrai.

Malgré tout, le seul LIKE "xxx*" sur un champ, en clé primaire de surcroît, est un poil plus efficace.

[f-leb]

regarde dans l'article: Indexation documentaire & bases de données.

Une recherche avec le mot clé "full text" ou "indexation full text" devrait te renseigner sur les possibilités en SQL.

[Richard_35]

Bonjour Fabien,

Merci pour le lien. Je lirai plus en détail le document cible ce week-end (il était un peu trad, hier). Effectivement, c'est une possibilité intéressante.

Néanmoins, cette méthode consiste, si j'ai bien compris, à rechercher des mots, et non des chaînes de caractères. Certes, les mots sont, tous, des chaînes de caractères, mais toutes les chaînes de caractères ne sont pas, forcément, des mots.

Dans ton post (#2), d'ailleurs, tu parles aussi de recherche de mots et non de recherche de chaîne de caractères.

Donc, la recherche avec l'indexation "full text" consisterait donc à rechercher :
- "petits" ;
- "pois" ;
- "orange" ;
...
et non :
- tspo ==> de Petits pois ;
- edesp ==> de Orange d'Espagne ;
- sch ==> de Pois chiche ;
...

Pour imager, voici la liste présentée précédemment, mais triée par la clé primaire (et non "physiquement") avec une flèche qui pointe sur les enregistrements concernés par ces trois exemples, lors du LIKE "xxx*" :

Code :

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
Libellé_Travaillé	Id_Produit
agne			456DEF
angedespagne		456DEF
che			789GHI
chiche			789GHI
despagne		456DEF
edespagne		456DEF  <==
espagne			456DEF
etitspois		123ABC
gedespagne		456DEF
gne			456DEF
hiche			789GHI
iche			789GHI
ischiche		789GHI
itspois			123ABC
ngedespagne		456DEF
ois			123ABC
oischiche		789GHI
orangedespagne		456DEF
pagne			456DEF
petitspois		123ABC
pois			123ABC
poischiche		789GHI
rangedespagne		456DEF
schiche			789GHI  <==
spagne			456DEF
spois			123ABC
titspois		123ABC
tspois			123ABC  <==

Bien entendu, le LIKE "xxx*" trouverait, instantanément, tous les produits qui comporteraient la même chaîne de caractères : ceux-ci se "suivraient".

En résumé, il s'agit d'une méthode de recherche de chaîne de caractères (que je n'ai d'ailleurs pas inventée) et non d'une méthode de recherche de mot, la seconde étant, en quelque sorte, "incluse" dans la première.