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

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:

---

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:

   ---

   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:

   ---

   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.

Salut,

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

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"

---

Bonsoir Fabien,

Citation:

---

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.

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.

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.