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

Requêtes MySQL Discussion :

optimiser jointure


Sujet :

Requêtes MySQL

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    40
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2006
    Messages : 40
    Par défaut optimiser jointure
    Bonsoir,
    Je dois faire une analyse du traffic du mois de janvier par tranche d'une heure sur les downloads d'un site.
    chaque objet en telechargement a un iditifiant id mais certain objets en ont deux , le meme objet est donc telechargeable sous deux identifiants differents.

    table objet:
    objet id id2
    AAA 1 60
    BBB 2 NULL
    CCC 3 NULL
    DDD 4 NULL
    EEE 5 61
    FFF 6 NULL

    mais ts les identifiants sont bien uniques.

    La table data contient tous les telechargements qui ont eu lieu.
    le champ 'timestamp' marque le debut du telechargement d'un objet et le champ 'url' est l'identifiant de l'objet.

    Voila donc ma requete:
    #1
    SELECT DATE_FORMAT( d.timestamp, '%d %H' ) date, count( * ) y
    FROM data d
    INNER JOIN objet o ON d.url IN (o.id, o.id2)
    WHERE d.timestamp
    BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59'
    GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' )
    ORDER BY `date` ASC

    Elle marche bien mais elle met bcp de temps à s'executer...
    en fait autant de temps qu'une requete faisant un produit cartesien.

    #2
    SELECT DATE_FORMAT( d.timestamp, '%d %H' ) date, count( * ) y
    FROM data d, objet o
    WHERE d.url IN (o.id, o.id2)
    AND d.timestamp
    BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59'
    GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' )
    ORDER BY `date` ASC

    le probleme semble venir de la double jointure sur le o.id et o.id2
    Si je fais la meme chose que la requete #1 mais avec une simple jointure
    INNER JOIN objet o ON d.url = o.id
    ou bien INNER JOIN objet o ON d.url o.id2
    la requete s'execute relativement rapidement...

    si vous voyez des ameliorations possibles, je suis preneur.
    Merci d'avance !

  2. #2
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut Re: optimiser jointure
    Extrait de la doc MySQL :

    expr IN (valeur,...)

    Retourne 1 si expr est l'une des valeurs dans la liste IN, sinon retourne 0. Si toutes les valeurs sont des constantes, toutes les valeurs sont évaluées avec le type de expr et triées. La recherche de l'élément est alors faite en utilisant la recherche binaire. Cela signifie que IN est très rapide si les valeurs contenues dans la liste IN sont toutes des constantes.
    Sauf que dans ton cas, les valeurs contenues dans la liste ne sont pas des constantes. Essaye ça:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    INNER JOIN objet o ON (d.url=o.id OR d.url=o.id2)
    Dans ce cas là, MySQL pourrait éventuellement s'aider des index, ce qui pourrait être un peu plus rapide (avec le IN, c'est impossible).






    Le problème de perfs peut aussi venir du GROUP BY. Compare les temps d'exécution avec et sans le GROUP BY pour voir son influence. Dans ce cas, tu peux essayer d'optimiser le GROUP BY: vu que tes téléchargements ont tous lieu le même mois, tu peux faire un GROUP BY sur le jour et l'heure seulement:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    GROUP BY DAY(d.timestamp), HOUR(d.timestamp)
    ou
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    GROUP BY DATE_FORMAT( d.timestamp, '%d %H')
    Compare les temps d'exécution de ces 2 méthodes.



    Allez, bonne chance !
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  3. #3
    Inactif   Avatar de Médiat
    Inscrit en
    Décembre 2003
    Messages
    1 946
    Détails du profil
    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 946
    Par défaut
    Je ne peux pas tester avec mySQL, et cela dépend peut-être de ta version, mais j'essaierais :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT laDate, SUM(y) 
    FROM (SELECT DATE_FORMAT( d.timestamp, '%d %H' ) laDate, count( * ) y 
          FROM data d INNER JOIN objet o ON d.url = o.id 
          WHERE d.timestamp BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59' 
          GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) 
         UNION ALL
          SELECT DATE_FORMAT( d.timestamp, '%d %H' ) laDate, count( * ) y 
          FROM data d INNER JOIN objet o ON d.url = o.id2 
          WHERE d.timestamp BETWEEN '2006-01-01 00:00:00' AND '2006-01-31 23:59:59' 
          GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) 
         )
    GROUP BY laDate
    ORDER BY laDate
    Pourquoi GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) et non GROUP BY DATE_FORMAT( d.timestamp, '%d %H' ) ?

  4. #4
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Citation Envoyé par Médiat
    Pourquoi GROUP BY DATE_FORMAT( d.timestamp, '%d %m %y %H' ) et non GROUP BY DATE_FORMAT( d.timestamp, '%d %H' ) ?
    C'est sympa de me plagier !
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  5. #5
    Inactif   Avatar de Médiat
    Inscrit en
    Décembre 2003
    Messages
    1 946
    Détails du profil
    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 946
    Par défaut
    Citation Envoyé par pcaboche
    C'est sympa de me plagier !
    Parce que tu crois que je te lis ?

  6. #6
    Membre averti
    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    40
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2006
    Messages : 40
    Par défaut
    Ah ca marche tres bien ca:
    INNER JOIN chainestv v ON ( t.url2 = v.sdp OR t.url2 = v.sdp2 )
    pourtant j'etais persuadé d'avoir essayer ,peut etre que je l'ai fait sans les parantheses...
    les requetes imbriquées ne fonctionnent pas dans ma version.
    Merci a vous

  7. #7
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Citation Envoyé par Médiat
    Parce que tu crois que je te lis ?
    Tu devrais: je n'écris pas QUE des conneries. En plus, ça éviterait les rédondances... surtout pour écrire des requêtes compliquées qui ne marchent pas (franchement, tu nous as habitué à mieux. Je suis presque déçu.)
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  8. #8
    Inactif   Avatar de Médiat
    Inscrit en
    Décembre 2003
    Messages
    1 946
    Détails du profil
    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 946
    Par défaut
    Citation Envoyé par pcaboche
    qui ne marchent pas
    Qui marchent (je reprends ton pluriel que je ne m'explique pas) très bien avec une base de données digne de ce nom, pas de ma faute si le posteur initial ne précise pas sa version.

    Citation Envoyé par pcaboche
    Je suis presque déçu.
    Pas moi, je te reconnais bien.

    Cette fois-ci je ne mets pas le smiley, puisque ton sens de l'humour semble inexistant !

  9. #9
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Tu as raison: j'aurais peut-être dû tout mettre au singulier (encore que ce sont des requêtes imbriquées en une seule, pour moi c'est comme si on avait plusieurs requêtes).

    Citation Envoyé par Médiat
    Cette fois-ci je ne mets pas le smiley, puisque ton sens de l'humour semble inexistant !
    Ah ok, c'était de l'humour. J'avais pris ça pour une aggression personnelle. Désolé!
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  10. #10
    Inactif   Avatar de Médiat
    Inscrit en
    Décembre 2003
    Messages
    1 946
    Détails du profil
    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 946
    Par défaut
    Citation Envoyé par pcaboche
    Ah ok, c'était de l'humour. J'avais pris ça pour une aggression personnelle. Désolé!
    Pas de problème, désolé que tu aies pu mal l'interpréter .

    Revenons à des considérations techniques : le OR et le IN sont tout à faits équivalents, il est donc très étonnant que mySQL sache gérer les index dans un cas et pas dans l'autre, c'est pour cela que j'avais fait une autre proposition dans laquelle j'étais certain que les index seraient utilisés (même s'il y a à la fin un GROUP BY sur des tables temporaires (< 800 lignes c'est négligeables)).

  11. #11
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Visiblement, le OR et le IN ne sont pas tout à fait équivalents.

    D'après ce qui est écrit dans la doc, si toutes les valeurs du IN sont des constantes, alors MySQL construit un arbre binaire pour accélérer la requête. Dans ce cas, le IN est globalement plus rapide que le OR (recherche binaire plutôt que élément par élément).

    Si par contre le IN contient des expressions (ici, des noms de colonnes), celui-ci sera transformé en une succéessions de OR, mais l'expérience montre que les index ne sont pas pris en compte (peut-être que les dernières versions de MySQL font ce type d'optimisation). Dans ce cas, le IN est plus lent que le OR.

    En partant de cette hypothèse, je me suis dit qu'on pouvait "faciliter" le travail de MySQL en réécrivant la jointure avec un OR (où là on est sûr qu'il utilisera les index, s'il y en a)
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  12. #12
    Membre averti
    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    40
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2006
    Messages : 40
    Par défaut
    en mettant des OR à la place de IN, je passe d'un temps d'execution d'une 10aines de minutes a une 20aines de secondes

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

Discussions similaires

  1. Optimiser Jointure lente
    Par mickael.camelot dans le forum Requêtes
    Réponses: 21
    Dernier message: 11/06/2013, 17h24
  2. Optimiser jointure + tri sur colonnes différentes
    Par Gaetch dans le forum Requêtes
    Réponses: 8
    Dernier message: 30/03/2012, 13h53
  3. Optimisation : Jointure externe ou interne ?
    Par argv666 dans le forum Décisions SGBD
    Réponses: 2
    Dernier message: 18/10/2005, 14h00
  4. Optimiser les jointures dans des requêtes
    Par klereth dans le forum PostgreSQL
    Réponses: 12
    Dernier message: 23/04/2005, 17h29
  5. Comment optimiser une jointure ?
    Par seb_asm dans le forum Administration
    Réponses: 21
    Dernier message: 25/06/2004, 16h42

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