Discussion :

[ithurts]

Bonjour,

Je cherche (desesperement) à optimiser cette jointure mer%#que qui mets à genou mon serveur :

L'idée est donc de recuperer l'ensemble des emails n'apartenant pas à une population définie par une autre table , je pense que la requête sera plus claire :

Code :

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

1
2
3
4
5
6
CREATE VIEW test AS
	SELECT email
	FROM contacts
	WHERE email NOT IN (SELECT DISTINCT email
			FROM conversions
			WHERE DATEDIFF(CURDATE(), conversion) < 7)

La table contacts a donc comme clef primaire "email"
La table conversions n'est pas dédupliqué sur les "email"s
Ca fait une semaine que je la tourne et retourne dans tous les sens je n'arrive pas à la rendre plus efficace ...
Comment la rendre "mieux" ?

Merci d'avance !

[GyZmoO]

Salut.

Tu peux sans doute utiliser une jointure externe, cf

http://sqlpro.developpez.com/cours/s...ntures/#LIII-C

[CinePhil]

L'idée est donc de recuperer l'ensemble des emails n'apartenant pas à une population définie par une autre table , je pense que la requête sera plus claire :

Code :

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

1
2
3
4
5
6
CREATE VIEW test AS
    SELECT email
    FROM contacts
    WHERE email NOT IN (SELECT DISTINCT email
            FROM conversions
            WHERE DATEDIFF(CURDATE(), conversion) < 7)

La table contacts a donc comme clef primaire "email"

Déjà une amélioration à faire : passe à une clé primaire auto-incrémentée !

La table conversions n'est pas dédupliqué sur les "email"s

Pas sûr de comprendre ce "dédupliqué" mais le but de la requête est d'oter les doublons d'une table ou de supprimer une information redondante (email) présente inutilement dans deux tables, c'est une bonne idée !

Je ne suis pas sûr que le DISTINCT soit nécessaire. Et plutôt qu'un NOT IN, préfère un NOT EXISTS.

Code :

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

1
2
3
4
5
6
7
8
9
CREATE VIEW test AS
    SELECT email
    FROM contacts
    WHERE NOT EXISTS (
        SELECT *
        FROM conversions
        WHERE conversions.email = contacts.email
            AND DATEDIFF(CURDATE(), conversion) < 7
        )

Et bien sûr ta table conversions porte un index sur la colonne email ?

[ithurts]

Bonsoir, et merci beaucoup pour vos réponses !

1. Déjà une amélioration à faire : passe à une clé primaire auto-incrémentée

   La table "contacts" possède déjà un ID associé à chaque email.
   Toutefois dans la table "conversions", le champ email apparaît dans des dizaines de milliers de tuples.
   Chaque tuple étant évidemment associé à un ID.
   
   De plus, les choses étant, il m'est impossible de remplacer email par son ID numérique dans la table conversions.
   Mais ce sera fait un jour, c'est sur et certain !
   

2. Je ne suis pas sûr que le DISTINCT soit nécessaire. Et plutôt qu'un NOT IN, préfère un NOT EXISTS.

   J'ai pensé qu'avec un DISTINCT, cela limiterait le nombre de tuples renvoyé par la requête SELECT , Allégeant ainsi le NOT IN !
   
3. Je ne connaissais pas cette utilisation du "NOT EXISTS", je vous remercie de l'astuce !
   J'ai pu gagner environ 10 secondes sur un select count(*) !
   

   Code :

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

   1
   2
   3
   4
   5
   6
   7
   8
   9
   CREATE VIEW test AS
       SELECT email
       FROM contacts
       WHERE NOT EXISTS (
           SELECT conversions.email
           FROM conversions
           WHERE conversions.email = contacts.email
               AND DATEDIFF(CURDATE(), conversion) < 7
           )

4. Et bien sûr ta table conversions porte un index sur la colonne email ?

   Plutôt deux fois qu'une !

Toutefois, mise à part remplacer les comparaisons de chaîne de caractères par des comparaisons de nombre.
Pensez vous qu'il existe d'autre moyen d'optimiser les choses ?
Je ne fait que des "select * ... into outfile ..." sur cette vue, peut être que préparer la requête associée puis directement la stoker dans la fichier est plus rapide ?

En vous remerciant d'avance.
Cordialement.

[CinePhil]

La table "contacts" possède déjà un ID associé à chaque email.

Toutefois dans la table "conversions", le champ email apparaît dans des dizaines de milliers de tuples. Chaque tuple étant évidemment associé à un ID.

De plus, les choses étant, il m'est impossible de remplacer email par son ID numérique dans la table conversions. Mais ce sera fait un jour, c'est sur et certain !

Ce serait une très bonne chose !
Clé primaire auto-incrémentée + clé étrangère faisant référence à la clé primaire donc de type entier.

J'ai pensé qu'avec un DISTINCT, cela limiterait le nombre de tuples renvoyé par la requête SELECT , Allégeant ainsi le NOT IN !

Au final ça fait moins de tuples mais le DISTINCT prend du temps à réaliser et nécessite de toute façon de parcourir toute la table.

Avec le NOT IN (SELECT DISTINCT..., le SGBD fait d'abord la sous-requête SELECT DISTINCT puis ensuite compare les valeurs des emails entre la sous-requête et la table.
Avec le NOT EXISTS, dès qu'une valeur d'email est trouvée dans la table conversions, le test s'arrête pour cette valeur d'email et le SGBD passe à la valeur suivante. Donc tous les tuples de conversions ne sont pas examinés systématiquement.

Je ne connaissais pas cette utilisation du "NOT EXISTS", je vous remercie de l'astuce !
J'ai pu gagner environ 10 secondes sur un select count(*) !

Code :

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

1
2
3
4
5
6
7
8
9
CREATE VIEW test AS
    SELECT email
    FROM contacts
    WHERE NOT EXISTS (
        SELECT conversions.email
        FROM conversions
        WHERE conversions.email = contacts.email
            AND DATEDIFF(CURDATE(), conversion) < 7
        )

SELECT * est suffisant. C'est même le seul cas où il est recommandé de l'utiliser. La requête signifie :
"Sélectionne les emails de la table contacts pour lesquels il n'existe pas de ligne dans la table conversions ayant la même valeur d'email et dont la date de conversion date de moins de 7 jours."
Inutile de préciser SELECT conversions.email, ce n'est pas utilisé par le SGBD.

Toutefois, mise à part remplacer les comparaisons de chaîne de caractères par des comparaisons de nombre.
Pensez vous qu'il existe d'autre moyen d'optimiser les choses ?

Tu peux faire un EXPLAIN de la requête (sans le CREATE VIEW) pour qu'on voie comment le SGBD l'exécute ?

[ithurts]

Bonjour,

Je vous remercie infiniment pour vos explications et vos astuces, je pense qu'aucun livre n'aurait pu mes les apprendre !
C'est pour ça que j'adore developpez.net

En ce qui concerne la clef étrangère, cela n'implique t'il pas une perte de performance sur les insert dans la table fille ?



Voici l'explain comme demandé

Code :

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

1
2
3
4
5
6
+----+--------------------+-------------+-------+---------------+---------+---------+--------------------+---------+--------------------------+
| id | select_type        | table       | type  | possible_keys | key     | key_len | ref                | rows    | Extra                    |
+----+--------------------+-------------+-------+---------------+---------+---------+--------------------+---------+--------------------------+
|  1 | PRIMARY            | contacts    | index | NULL          | PRIMARY | 182     | NULL               | 4060840 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | conversions | ref   | email         | email   | 181     | contacts.email     |  278403 | Using where              |
+----+--------------------+-------------+-------+---------------+---------+---------+--------------------+---------+--------------------------+

Je dois avouer que je suis hyper curieux de savoir ce qu'on peut tirer de si peu d'information !

[CinePhil]

Je dois avouer que je suis hyper curieux de savoir ce qu'on peut tirer de si peu d'information !

Pas mal de choses...
- Tes tables ne sont pas petites ! 4 millions de lignes pour une, ça commence à faire ! Et quand il faut comparer à une autre table de 278 403 lignes, ça augmente encore le travail.

- Ta colonne email a une taille de 182 octets ? Un identifiant auto-incrémenté prendrait seulement 4 octets ! Tu commence à voir l'économie qui pourrait être faite en recherche ?

Essaie de mettre un index sur la colonne conversion. Ca pourrait permettre au SGBD de commencer par chercher les valeurs qui répondent à la condition WHERE dans la sous-requête et donc diminuerait d'autant le nombre de lignes examinées. Actuellement, si ta table conversions fait effectivement 278 403 lignes, c'est que la sélection sur la date est faite en dernier.

Je ne suis pas un expert de l'interprétation des EXPLAIN mais c'est ce que je crois comprendre. Peut-être qu'un plus expérimenté que moi aura d'autres idées.