Discussion :

[viny]

Bonjour,

Voici une requête très simple qui verifie l'unicité de l'adresse mail dans ma table. les adresses sont cryptées et cela met plus de 15 secondes sur 11 000 enregistrement pour me retourner un résultat.

Les emails sont stockées dans un champ de type Text Unique avec index.

Voici la requête :

Code :

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

1
2
3
4
 
SELECT id_membre
FROM inscrit
WHERE pgp_sym_decrypt(dearmor(membre_email),$2) = $1;

$2 = clé de cryptage
$1 = email à vérifier

Voici l'analyse de la requête :

Code :

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

Seq Scan on inscrit  (cost=0.00..2683.30 rows=56 width=8) (actual time=1809.930..14227.619 rows=1 loops=1)

Comment améliorer la rapidité selon vous car c'est le cryptage qui doit plomber la rapidité ?

merci !

[scheu]

Décidément tes problèmes de perf ça n'arrête pas
Ta clé de cryptage est la même pour tous tes enreg ? Si oui tu peux essayer de créer un index de fonction sur pgp_sym_decrypt(dearmor(membre_email),ta_clé)

[viny]

Salut scheu !

Ben oui j'essaie d'optimiser tout cela !

Oui la clé de cryptage est la même pour tous !

On crée comment un index de fonction ????

[j_f_b]

Moi, j'aurai plutôt fait le contraire: chiffrer la clause de comparaison et la comparer à l'ensemble des valeurs dans la base plutot de de déchiffrer l'ensemble des valeurs de la base(ce qui sera forcement assez couteux) pour les comparer à une valeur non chiffrée

[viny]

J'ai fait le temps d'exécution est le même...

[viny]

J'ai créé un index sur la fonction :

Code :

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

1
2
 
CREATE INDEX unicite_email ON inscrit  (pgp_sym_decrypt(dearmor(inscrit_email),'maclé'));

C'est parfait les temps de réponse sont super !!! merci scheu !

[scheu]

J'ai créé un index sur la fonction :

Code :

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

1
2
 
CREATE INDEX unicite_email ON inscrit  (pgp_sym_decrypt(dearmor(inscrit_email),'maclé'));

Cela n'améliore absolument rien...

Si tu faits

Code :

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

1
2
3
SELECT id_membre
FROM inscrit
WHERE pgp_sym_decrypt(dearmor(membre_email),'clé') = valeurdetest

passe-t-il par l'index ? As-tu (re)calculé les stats ?

[viny]

J'avais laissé la clé en paramètre dans ma fonction donc l'index ne fonctionnait pas.

C'est dommage d'ailleurs qu'il faille mettre la clé dans la fonction et plsu ne paramètre pour que cela fonctionne ...

[j_f_b]

J'ai fait le temps d'exécution est le même...

Comment l'as-tu fait ? en faisant un appel à la fonction de chiffrement dans la clause where ? Alors en effet, le résultat est le même car le chiffrement est refait pour chaque enregistrement sauf si ta fonction de chiffrement est déclarée comme "Stable strict"
essaie en calculant d'abord la valeur chiffrée et en la passant dans ta requète en dur ensuite, avec un index sur les valeurs chiffrées. Si cela améliore tes perf, tu pouras passer par une petite procedure

Créer un index avec la fonction et la clé, revient à indexer les valeurs déchiffrée, ce qui n'est probablement pas la meilleure idée en terme de sécuritée. Enfin si tu les chiffres j'imagine que tu as une bonne raison de le faire.

[viny]

Tu peux me donner un exemple de ce que tu me conseilles de faire car je vois pas trop le code à écrire.

merci

[j_f_b]

étape 1: récupérer la valeur chiffrée correspondant une adresse précise que tu veux tester:

Code :

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

SELECT pgp_sym_encrypt('email', 'maclé');

cela te retourne une valeur, appelons là valeur1 (requète à corriger/adapter en fonction du nom de la fonction et de la méthode pour l'appeler)

étape 2: tester la requète

Code :

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

1
2
3
SELECT id_membre
FROM inscrit
WHERE membre_email = 'valeur1';

en ayant bien un index sur la colonne membre_email

ça donne quoi ?

[viny]

Effectivement c aussi rapide et je préfère cette solution car ma clé reste toujours en paramètre !!!

mais y a un pb !

[j_f_b]

d'abord, est-ce que ça fonctionne à la main (hors procédure, on verra après comment l'écrire si oui) ?

[viny]

Ca fonctionne à la main ?

La première solution avec l'index sur fonction oui fonctionne très bien la deuxième solution non...

[dk]

Comparer des chaines c'est coûteux en performance.

Pour contourner ton probleme, je ferais à ta place :

1 - Créer un champ supplémentaire 'email_tronque' avec seulement les 5 premiers caractères de ton email crypté

2 - Comparer les 5 premiers caractères du mail crypté entré par l'utilisateur avec ce champ 'email_tronque'.

3 - Ensuite suivant le nombre de résultat :

0 : l'utilisateur n'existe pas
>0 : il faut faire un autre select avec cette fois ci l'email crypté en entier sur les qqs résultats trouvés.


bon courage

[viny]

Non non c'est pas couteux avec la solution de Scheu c'est hyper rapide...