IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

Eviter left join


Sujet :

Langage SQL

  1. #1
    Membre actif
    Profil pro
    Inscrit en
    Mai 2004
    Messages
    551
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2004
    Messages : 551
    Points : 264
    Points
    264
    Par défaut Eviter left join
    Bonjour,

    Sur du MariaDB, je connais bien LEFT JOIN mais par les sous requêtes. J'ai deux tables

    Table users:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
        +--------------------------+-----------------+------+-----+---------+----------------+
        | Field                    | Type            | Null | Key | Default | Extra          |
        +--------------------------+-----------------+------+-----+---------+----------------+
        | user_id                  | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
        | user_activation_key      | varchar(40)     | YES  |     | NULL    |                |
        | user_email               | varchar(40)     | NO   | UNI |         |                |
        | user_login               | varchar(30)     | YES  |     | NULL    |                |
        | user_password            | varchar(40)     | YES  |     | NULL    |                |
        | user_firstname           | varchar(30)     | YES  |     | NULL    |                |
        | user_lastname            | varchar(50)     | YES  |     | NULL    |                |
        | user_lang                | varchar(2)      | NO   |     | en      
        +--------------------------+-----------------+------+-----+---------+----------------+
    Et table users_oauth:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    +----------------------+-----------------+------+-----+---------+----------------+
    | Field                | Type            | Null | Key | Default | Extra          |
    +----------------------+-----------------+------+-----+---------+----------------+
    | oauth_id             | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | oauth_user_id        | int(8) unsigned | NO   |     | NULL    |                |
    | oauth_google_id      | varchar(30)     | YES  | UNI | NULL    |                |
    | oauth_facebook_id    | varchar(30)     | YES  | UNI | NULL    |                |
    | oauth_windowslive_id | varchar(30)     | YES  | UNI | NULL    |                |
    +----------------------+-----------------+------+-----+---------+----------------+
    users.user_id et users_oauth.oauth_user_id permet de lier ces deux tables.

    Il y a un index fulltext pour la table users comme ceci:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER TABLE `users` ADD FULLTEXT KEY `fulltext_adminsearch` (`user_email`,`user_firstname`,`user_lastname`);
    La requêtes suivant sans le LEFT JOIN prends 0.0360 secondes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT users.* FROM users WHERE ( 
     
           (MATCH (user_email, user_firstname, user_lastname) AGAINST ('+john + smith')) ) 
     
    ORDER BY user_id DESC LIMIT 0,50
    Mais si j'ajoute un LEFT JOIN pour avoir le résultat de users_oauth je passe à 3.0237secondes (83 fois plus lent):
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE (
     
                                     (MATCH (user_email, user_firstname, user_lastname) AGAINST ('+john + smith')) ) 
     
                                     ORDER BY user_id DESC LIMIT 0,50
    Il y a quand même une grosse différence entre rouler à 10 km/h et rouler à 830 km/h.

    Limite, ce serait plus rapide de faire une requête SQL par résultat (50) plutôt que le LEFT JOIN.

    Comment faire une sous requête pour éviter le LEFT JOIN ? J'aimerais voir si ce sera plus rapide.

    Merci,
    Vincent.

    PS, pour les tests, il y a environ 150 000 entrées dans la table users et 18 000 dans la table users_oauth

  2. #2
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juillet 2016
    Messages
    2 757
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Juillet 2016
    Messages : 2 757
    Points : 10 695
    Points
    10 695
    Billets dans le blog
    21
    Par défaut
    Bonjour,

    La question est : y a-t-il un index sur oauth_user_id ? Si non, commence par en ajouter un et regarde les performances.
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  3. #3
    Membre actif
    Profil pro
    Inscrit en
    Mai 2004
    Messages
    551
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2004
    Messages : 551
    Points : 264
    Points
    264
    Par défaut
    Pfff, oui j'ai oublié de mettre un index sur users_oauth.user_id !

    ...et les performances ont augmenté. Je suis passé à 0.6615 secondes pour cette requête :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE (
     
                      users.user_id LIKE 'John Smith' OR 
                      (MATCH (user_email, user_firstname, user_lastname) AGAINST ('+"John" +"Smith"' IN BOOLEAN MODE)) ) 
     
    ORDER BY user_id DESC LIMIT 0,50
    Ensuite, j'ai modifié la requête avec un UNION ALL :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
     
    SELECT users.*FROM (
                    (
                      SELECT * FROM users
                      WHERE user_id like 'John smith'
                      ORDER by user_id  
                      LIMIT 0,50
                     )
                    UNION ALL 
                    (
                    SELECT * FROM users
                    WHERE MATCH (user_email, user_firstname, user_lastname) AGAINST ('+"John" +"smith"' IN BOOLEAN MODE) 
                    ORDER by user_id  
                   LIMIT 0,50
                   )
    	  )
         AS users
             LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id
        ORDER BY user_id DESC   
        LIMIT 0,50
    Et celle-ci s'exécute en 0.7475 secondes.

    Elle est légèrement plus lente mais je la trouve mieux écrite avec un UNION ALL pour séparer la recherche sur user_id et sur le reste (email, firstnam, lastname).

    Vous en pensez quoi ?

    Merci,
    Vincent.

  4. #4
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juillet 2016
    Messages
    2 757
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Juillet 2016
    Messages : 2 757
    Points : 10 695
    Points
    10 695
    Billets dans le blog
    21
    Par défaut
    Tout d'abord, LIKE et = sont deux opérateurs différents. Si tu recherches une comparaison exacte, = sera plus approprié que LIKE.

    Ensuite, tu essaies de comparer un INT à une chaîne de caractères (ici, "John Smith"). Le résultat sera donc toujours faux.

    Et détrompes toi, la première requête que tu as écrites est bien plus lisible et maintenant que la seconde, et le SGBD pourra mieux l'optimiser (tes tests le confirme déjà en plus)
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  5. #5
    Membre actif
    Profil pro
    Inscrit en
    Mai 2004
    Messages
    551
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2004
    Messages : 551
    Points : 264
    Points
    264
    Par défaut
    Oui je comprends, sur le site, il y a un seul et même input qui permet de rechercher à la fois un ID, un prénom, un nom et un email. Donc j'avais mis LIKE parce qu'on peut se retrouver avec 'John Smith' dans la requête comme on peut se retrouver avec un INT, comme ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    SELECT users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE (
     
                      users.user_id = '146182' OR 
                      (MATCH (user_email, user_firstname, user_lastname) AGAINST ('+"146182"' IN BOOLEAN MODE)) ) 
     
    ORDER BY user_id DESC LIMIT 0,50
    Et en effet, cela fonctionne bien aussi avec = et cela semble plus approprié.

    Merci pour ton aide,
    Vincent.

  6. #6
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juillet 2016
    Messages
    2 757
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Juillet 2016
    Messages : 2 757
    Points : 10 695
    Points
    10 695
    Billets dans le blog
    21
    Par défaut
    Si tu as deux utilisations bien distinctes (recherche par id et recherche par nom prénom), je te conseille alors d'avoir deux requêtes distinctes. Tu gagneras en clartés et en maintenance.
    Par exemple, quand tu recherches un utilisateur à partir d'un ID, tu n'auras qu'un seul enregistrement (j'espère !) tandis que par nom prénom, tu peux en avoir plusieurs.

    Cela reste compatible avec avoir un seul champ côté interface utilisateur. C'est juste qu'avant d'exécuter ta requête, tu regardes ce que l'utilisateur a passé en paramètre. Si c'est un entier, tu fais une recherche sur l'id, dans le cas contraire, tu utilises ta recherche fulltext.
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. count() dans *plusieurs* LEFT JOIN
    Par silver_dragoon dans le forum Langage SQL
    Réponses: 2
    Dernier message: 28/06/2004, 18h20
  2. LEFT JOIN avec Oracle 8i ne va pas... doit utiliser (+)
    Par loikiloik dans le forum Langage SQL
    Réponses: 10
    Dernier message: 21/04/2004, 17h38
  3. Interbase et left join
    Par Zog dans le forum Bases de données
    Réponses: 4
    Dernier message: 23/03/2004, 09h55
  4. Non coincident MySQL (Left Join)
    Par Remiguel dans le forum Requêtes
    Réponses: 6
    Dernier message: 03/11/2003, 22h25
  5. Export d'une vue avec LEFT JOIN
    Par schnourf dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 22/05/2003, 14h57

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo