Discussion :

[trucmuche2005]

Bonjour,

J'ai une requète du type

Code :

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

1
2
3
4
5
SELECT DISTINCT table.id, madistance(table.email, 'adresseemail@exemple.com') as distance
FROM table
WHERE madistance(table.email, 'adresseemail@exemple.com')<6
ORDER BY distance ASC
LIMIT 5

Dans mon cas, le calcul de la fonction "madistance" est long et j'aimerais voir comment je peux accélérer cette requête, qui prend actuellement 8 secondes (hum).

Première question : lorsque MySQL trouve une ligne pour laquelle "madistance(table.email, 'adresseemail@exemple.com')<6", est-ce qu'il recalcule une deuxième fois cette valeur pour la stocker dans la colonne "distance", ou bien il ne fait ce calcul qu'une seule fois ?

Seconde question : est-ce que la présence de "LIMIT 5" permet d'accélérer les choses ou pas ? Autrement dit, est-ce qu'il s'arrête lorsqu'il a trouvé les 5 lignes demandées ou bien il cherche d'abord toutes les lignes puis après les avoir trouvées, il ne retourne que les 5 premières ?

Mille mercis pour vos infos !!

T.

[tbc92]

Je ne sais pas répondre à la 1ère question.
Mais pour la 2ème question, comme tu fais un order by, il est obligé de calculer la distance pour tous les enregistrements, puis faire un tri sur le résultat de ce calcul, et enfin afficher uniquement les 5 premières lignes.

Si tu faisais ceci :

Code :

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

1
2
3
4
5
6
7
 
SELECT * from 
( SELECT  table.id, madistance(table.email, 'adresseemail@exemple.com') as distance
FROM table
WHERE madistance(table.email, 'adresseemail@exemple.com')<6
LIMIT 5)
ORDER BY distance ASC

Dans ce cas, j'imagine que le moteur calcule la distance, compare avec le nombre 6, s'arrête dès qu'il a 5 lignes qui vérifient le critère, puis trie ces 5 lignes.
Dans ce 2nd scénario, si ça ce passe comme je l'imagine, tu aurais 5 lignes triées, mais pas forcément les 5 meilleures lignes.

Enfin, si le distinct n'est pas nécessaire, autant l'enlever. Lui aussi consomme du temps.

[SQLpro]

Le meilleur moyen de plomber les performances d'un SGBDR quel qu'il soit est de lui faire faire les choses itérativement alors qu'il fonctionne de manière ensembliste.
Votre fonction oblige le serveur a effectuer des itérations alors que transformé en requête, cela deviendrais du code ensembliste !

Donc un peu d'effort pour transformer votre code de fonction et tout rentrera dans l'ordre.
Vous serez même surpris des performances, malgré que MySQL soit l'un des SGBD les plus lents !

A +

[trucmuche2005]

@ tbc92 : oui, je comprends ; mais s'il me sort les 5 résultats les plus mauvais juste parce que ce sont les 5 premiers, cela ne m'arrange pas non plus :-D

@ SQLpro : Merci pour vos encouragements. Cela ne m'aide toutefois pas beaucoup d'un point de vue concret... mais bon, c'est déjà ça hein et je me sens au moins soutenu :-D je vais chercher ailleurs pour de l'aide, du coup...

[SQLpro]

@ tbc92 : oui, je comprends ; mais s'il me sort les 5 résultats les plus mauvais juste parce que ce sont les 5 premiers, cela ne m'arrange pas non plus :-D

Remplacez ASC par DESC dans la requête !

@ SQLpro : Merci pour vos encouragements. Cela ne m'aide toutefois pas beaucoup d'un point de vue concret... mais bon, c'est déjà ça hein et je me sens au moins soutenu :-D je vais chercher ailleurs pour de l'aide, du coup...

Postez le code de votre fonction....

A +

[trucmuche2005]

@ SQLPro : Remplacer ASC par DESC ? certainement pas... Ce qui m'intéresse, ce sont ceux qui ont les plus petites valeurs pour la colonne "distance" donc ASC a du sens pour moi, pas DESC. Ce à quoi je réagissait était la remarque de tbc92 qui disait qu'avec sa requête en deux temps (d'abord le LIMIT puis ensuite le ORDER), sa requète allait s'arrêter après avoir trouvé 5 lignes satisfaisant à la clause WHERE puis qu'il allait les trier. Cela ne m'arrange pas car si les 5 premières lignes qu'il trouve sont des lignes avec des valeurs de "distance" qui ne sont pas les meilleures, il va retenir 5 lignes d'accord, mais pas celles qui m'intéressent. Donc ça ne me convient pas...

Quant à la fonction en question, il s'agissait de la fonction "levenshtein" de MySQL. Je sais que c'est lent mais les deux questions que je pose demeurent, il me semble, même si la fonction "madistance" était autre chose... Ce que je me demande ici, c'est de comprendre comment MySQL fait le genre de requête que j'ai posté, avec le ORDER et le LIMIT...

[tbc92]

Je réexplique ce que j'essayais d'expliquer.
Avec ta requête, tu calcules la distance pour toutes les combinaisons. Puis tu tries sur le résultat de ce calcul, puis tu gardes les 5 meilleures lignes.

Pour illustrer le fait que tu calcules la distance sur tous les enregistrements, je proposais une autre requête, qui ne calculait pas la distance sur tous les enregistrements. Mais bien évidemment, cette 2ème requête renverrait un résultat non conforme au besoin. Je pensais que ce dernier point était clair.

Mais, question à SqLPro ,
Je suis très intéressé par plus d'explications sur la manière 'ensembliste'. Je ne connais pas, et j'ai certainement plein de choses à apprendre.

[trucmuche2005]

Pour illustrer le fait que tu calcules la distance sur tous les enregistrements, je proposais une autre requête, qui ne calculait pas la distance sur tous les enregistrements.

Précisément, je ne comprends pas bien en quoi ta sous-requête calcule moins la fonction que la mienne. En particulier, il me semble que tu as tort d'affirmer "Avec ta requête, tu calcules la distance pour toutes les combinaisons. Puis tu tries sur le résultat de ce calcul, puis tu gardes les 5 meilleures lignes."
En effet, j'ai trouvé ceci dans le manuel MySQL :

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

Du coup, il me semble que MA requête avec le ORDER BY + LIMIT 5 calcule la fonction madistance pour les 5 lignes (ou 10, c'est ma question #1) qui possèdent la distance la plus faible (et c'est ce que je souhaite)... pas une ligne de plus.

Quant à ta sous-requête avec LIMIT mais sans ORDER, par contre, je ne vois pas encore clair sur son fonctionnement : est-ce qu'elle s'arrête après avoir regardé 5 lignes (comme il n'y a pas de ORDER, cela ne me semble pas précisé à cet endroit dans le manuel) ou bien est-ce qu'elle regarde toutes les lignes pour n'en sortir que les 5 premières ? (mais quoi qu'il en soit, ta requête ne convient pas pour mon usage et c'était très clair dès que tu l'avais postée, en effet).

[SQLpro]

Le calcul de distance des littéraux par un LEVENSHTEIN n'est absolument pas adapté aux mondes des bases de données. Il est en effet massivement récursif donc ultra itératif et par conséquent plombe dramatiquement les performances des requêtes.

Je ne m'étonne même pas que les développeurs MySQL aient pensé à mettre cette fonction au catalogue de MySQL ! Sachant que ce SGBD est l'un des plus mauvais à tous niveau (lenteur, bugs, erreurs...).

D'autre part si vous effectuiez des études préalables au lieu de foncer tête baissé dans le code, vous auriez sans doute remarqué qu'ici même cette problématique a été traitée avec les mêmes conséquences... L'abandon d'utilisation de cette fonction pour en utiliser une autre dont je suis l'inventeur...

Il suffit de chercher !

A +

[SQLpro]

Mais, question à SqLPro ,
Je suis très intéressé par plus d'explications sur la manière 'ensembliste'. Je ne connais pas, et j'ai certainement plein de choses à apprendre.

MySQL n'est déjà pas vraiment ensembliste....

Pour en comprendre les enjeux et l'intérêt, voici un exemple simple sur lequel MySQL par en erreur :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE T_UNIK (C INT CONSTRAINT PK PRIMARY KEY); 
 
INSERT INTO T_UNIK VALUES (1), (2), (3); 
 
SELECT * FROM T_UNIK; 
 
UPDATE T_UNIK SET C = C + 1;

A +

[trucmuche2005]

D'autre part si vous effectuiez des études préalables au lieu de foncer tête baissé dans le code, vous auriez sans doute remarqué qu'ici même cette problématique a été traitée avec les mêmes conséquences... L'abandon d'utilisation de cette fonction pour en utiliser une autre dont je suis l'inventeur... Il suffit de chercher !

allez et sinon, on s'est mal réveillé ce matin ? ah non c'est tous les jours pareil :-) C'est pas grave, je suis habitué maintenant à vos jugements suffisants :-) Heureusement que je ne suis pas trop sensible aux problèmes d'ego, ça me fait rire :-) Ma finalité n'est pas la même que la vôtre, c'est tout :-) Et que vous le réalisiez ou non, que vous le compreniez ou non, que vous l'acceptiez ou non, peu me chaut :-)

Cela dit, j'ai bien sûr trouvé ce dont vous parlez et on en a déjà discuté sur un autre topic, SQLPro, puisque j'imagine que vous faites allusion à votre si superbe "inférence basique" pour laquelle je vous ai déjà interpellé d'ailleurs, sans réaction de votre part, au sujet de l'écriture du bloc "SET @I = @I + 1 // CONTINUE // END // SET @I = @I + 1" qui me paraît indigne de votre grande éminence, si je comprends bien l'utilité du mot "CONTINUE" (voir ici) - et c'est possible que non...

Je savais également qu'en citant le nom "levenshtein", j'aurais droit à des commentaires hautains. Mais si Levenshtein est une preuve, il me semble que c'est du fait qu'il n'est pas toujours possible d'écrire des fonctions "ensemblistes". Merci également pour votre exemple qui démontre bien en quoi MySQL est une merde, je vous l'accorde, c'est une merde. Mais ce n'est pas ma question ici et ca me ferait bien plaisir si on pouvait passer l'étape "je-commence-par-dire-tout-le-mal-que-je-pense" pour tout de suite aller un cran plus loin dans la réflexion et la problématique qui occupe celui qui pose la question et qui n'a pas toujours la possibilité de faire ce qu'il veut.

Je répète que j'aimerais comprendre comment MySQL se comporte pour évaluer les requètes du genre :

Code :

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

1
2
3
4
5
SELECT DISTINCT table.id, mafonction(table.email) as nombre
FROM table
WHERE mafonction(table.email)<6
ORDER BY nombre ASC
LIMIT 5

Il me semblait avoir compris dans une discussion précédente que, de toute manière, quelle que soit la fonction ensembliste ou non, cette grosse m**** puan** de MySQL que vous détestez tellement que cela doit vous faire mal au ventre rien qu'à l'idée de rentrer dans ce forum va évaluer comme une conn* "mafonction" pour plein d'arguments alors que ce n'est pas nécessaire. Mais j'ai peut-être mal compris.
Ma question principale en lien avec cet exemple est : la fonction "mafonction" va-t-elle être évaluée autant de fois qu'il y a de lignes dans la table ? le double de fois ? ou juste 5 ou 10 fois ?

Rien de plus, rien de moins. Et si la réponse est : "on ne peut pas répondre à cette question sans connaître le code de 'mafonction'.", ben alors ca m'intéresse de savoir pourquoi.

[SQLpro]

... Ma question principale en lien avec cet exemple est : la fonction "mafonction" va-t-elle être évaluée autant de fois qu'il y a de lignes dans la table ? le double de fois ? ou juste 5 ou 10 fois ?

Rien de plus, rien de moins. Et si la réponse est : "on ne peut pas répondre à cette question sans connaître le code de 'mafonction'.", ben alors ca m'intéresse de savoir pourquoi.

Encore une fois il faudrait avoir accès au code de la fonction.

Par exemple une fonction peut être déterministe ou non déterministe. En général et par défaut les fonctions sont considérées comme non déterministes de prime abord. (je généralise ne connaissant pas toutes les méandre de MySQmerde). Dans ce cas la fonction est appliquée sur chaque ligne.

Certains SGBDR permettent de créer des fonctions avec la directive DETERMINISTIC qui permet d'optimiser l'exécution de la fonction. Par exemple appliquée sur un SCAN d'index, la fonction n'est calculée que si il existe une différence d'occurrence d'une ligne à l'autre de la lecture. Ce qui permet d'optimiser l'exécution et d'utiliser l'index.
C'est le cas général de certaines fonctions intégrées de certains SGBDR...
Là encore je doute que l'optimiseur de MySQL sache faire quoique ce soit d'équivalent.

A +

[Artemus24]

Salut à tous.

1) on ne sait rien de votre fonction. Il nous est donc difficile de l'améliorer.

2) (question 1)MySql calcule 1 seule fois la distance pour 1 ligne donnée.

3) (question2) par contre, et je rejoins SQLPRO, il va calculer la distance pour toutes les lignes de votre table, même si vous en retenez que 5.
comme votre critère est de récupérer les cinq distances les plus petites, pour savoir le faire, MySql est obliger de les calculer toutes.

4) Je ne connais pas la fonction "levenshtein" de MySQL. Mais pourquoi mettre cette fonction dans une requête ?
Ne serait-il pas plus judicieux de calculer cette distance lors de la création de la ligne ?
En conséquence de quoi, la performance de votre requête serait meilleure.

Ma question principale en lien avec cet exemple est : la fonction "mafonction" va-t-elle être évaluée autant de fois qu'il y a de lignes dans la table ? le double de fois ? ou juste 5 ou 10 fois ?

J'ai répondu ci-dessus.
Votre fonction sera traité 1 et 1 seule fois par ligne présente dans votre table.
Si vous avez 100.000 lignes, la fonction sera traité 100.000 fois dans votre requête.

La meilleure façon pour s'assurer que la fonction ne sera appelé qu'une seule fois est décrire votre requête ainsi :

Code :

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

1
2
3
4
5
6
SELECT    DISTINCT table.id,
                   madistance(table.email, 'adresseemail@exemple.com') as distance
    FROM  table
  HAVING  distance < 6
ORDER BY  distance ASC
LIMIT 5

Encore une fois il faudrait avoir accès au code de la fonction.

Dans le cadre de la question posée, non, il n'est pas nécessaire de connaitre le contenu de cette fonction.

Dans ce cas la fonction est appliquée sur chaque ligne.

Pourquoi feindre de ne pas savoir répondre à la question si vous connaissez la réponse ?

Certains SGBDR permettent de créer des fonctions avec la directive DETERMINISTIC qui permet d'optimiser l'exécution de la fonction.

Ce n'est pas la question posée !

Il est demandé combien de fois appelle-t-on la fonction pour 1 ligne donnée ?
Et la réponse n'a toujours pas changée : 1 ligne = 1 appel à la fonction.

@+

[SQLpro]

4) Je ne connais pas la fonction "levenshtein" de MySQL. Mais pourquoi mettre cette fonction dans une requête ?
Ne serait-il pas plus judicieux de calculer cette distance lors de la création de la ligne ?

Non, le levenshtein (à me lire : http://sqlpro.developpez.com/cours/s...ns-motifs/#LII) dépend deux deux arguments, le premier étant certainement dans la table, le second étant variable....

[...]
Il est demandé combien de fois appelle-t-on la fonction pour 1 ligne donnée ?
Et la réponse n'a toujours pas changée : 1 ligne = 1 appel à la fonction.

C'est sans doute ce que fait MySQmerde, mais certains autres SGBDR avec une fonction de type déterministe ne les calculent pas toutes...

Bref, pour avoir des performances, changez de SGBDR, par exemple pour SQL Server !!!

A +

[Artemus24]

Salut SQLPRO.

C'est sans doute ce que fait MySQmerde, mais certains autres SGBDR avec une fonction de type déterministe ne les calculent pas toutes...

Je suis d'accord que le type deterministe permet d'améliorer les performances de la requête, mais ce n'est pas la question qui a été posée.

Je vais lire votre article au sujet de LEVENSHTEIN.

@+