1. #1
    Futur Membre du Club
    Profil pro
    Inscrit en
    octobre 2012
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2012
    Messages : 11
    Points : 5
    Points
    5

    Par défaut Optimiser un bête SELECT qui mets plus de 2 secondes

    Bonjour,

    J'ai une requête pourtant simple sur mon site, mais qui est vraiment très lente suite à la liaison avec une seconde table et j'aimerai savoir si je peux améliorer cela.

    J'ai une table personnalites avec 80000 entrées et une table personnalites_pays avec 82000 entrées, car une personnalité peut avoir plusieurs pays.

    personnalites
    - id
    - nom

    id =>PRIMARY KEY
    nom => INDEX

    personnalites_pays
    - id_personnalite
    - id_pays

    id_pays => INDEX
    id_personnalite => INDEX
    id_personnalite + id_pays => PRIMARY KEY

    Ma requete est toute bête : je liste les personnalités en filtrant par un pays, avec une pagination (affichés 50 par 50), et ordonné par nom.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT p.nom FROM personnalites AS p, personnalites_pays AS pp WHERE p.id = pp.id_personnalite AND pp.id_pays = 1 GROUP BY p.id ORDER BY p.nom ASC LIMIT 50000,50
    Cette requête mets généralement plus de 2 secondes :/ (vérifié avec SQL_NO_CACHE)
    J'ai vérifié avec EXPLAIN j'ai bien la key id_pays de personnalite_pays d'utilisée pour la jointure et la primary key id pour la table personnalites
    Si j'enlève le ORDER BY p.nom, ça descend autour de 0.5s
    Si j'enlève la jointure avec la table des pays, ça descend autour de 0.4s
    Mes deux tables sont en MyISAM.
    Le serveur dédié est pas mal pourtant (un ovh à 170€ par mois et la BDD tourne sur un SSD) c'est la seule requête lente que j'ai car la table personnalites est conséquente maintenant.

    Est ce que c'est optimisable ?

    Merci.

  2. #2
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 614
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 614
    Points : 9 351
    Points
    9 351

    Par défaut

    bonjour,

    enlevez déjà le GROUP BY.

    Combien y a-t-il de lignes dans personnalites_pays et combien avec id_pays = 1 ?

  3. #3
    Futur Membre du Club
    Profil pro
    Inscrit en
    octobre 2012
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2012
    Messages : 11
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par aieeeuuuuu Voir le message
    bonjour,

    enlevez déjà le GROUP BY.

    Combien y a-t-il de lignes dans personnalites_pays et combien avec id_pays = 1 ?
    Merci de la réponse. J'ai 82000 entrées dans personnalite_pays. Et j'ai 54000 personnalités dont le pays = 1.

    Effectivement sans le GROUP BY je descend à 0.6s (ça reste encore long mais c'est beaucoup mieux, si je mets un DISTINCT à la place c'est aussi long que le GROUP BY au passage), mais je l'avais mis ce GROUP BY pour une raison je pense (ma requête provient d'un moteur de recherche de personnalité et il est possible de filtrer par pas mal d'autres critères qui font des jointures avec d'autres tables et je crois que c'était utile pour ça).

  4. #4
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Bonsoir

    3 remarques au sujet de votre GROUP BY :

    - selon la norme SQL, le groupage doit être cohérent avec les colonnes du SELECT, ce n'était pas le cas dans votre requête. MySQL est très permissif mais tout autre SGBD n'aurait pas accepté cette requête. A titre d'exemple, SELECT COL1, COL2, SUM(COL3) FROM TAB1 GROUP BY COL1, COL2 est correct, alors que SELECT COL1, SUM(COL3) FROM TAB1 GROUP BY COL2 ne l'est pas.

    - faire un group by sans besoin d'agrégation (SUM, AVG, MIN, MAX...) ne présente aucun intérêt autre que pénaliser les performances

    - comme votre critère de groupage était l'id, qui par définition est unique puisque PK, faire un GROUP BY ne sert à rien dans ce cas

  5. #5
    Futur Membre du Club
    Profil pro
    Inscrit en
    octobre 2012
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2012
    Messages : 11
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par escartefigue Voir le message
    Bonsoir

    3 remarques au sujet de votre GROUP BY :
    .........
    Merci de la réponse. En fait en postant ici, j'ai volontairement simplifié ma requête; en vrai je faisais un SELECT p.id, p.nom etc.

    En fait le GROUP BY est utile car j'ai aussi de lié aux personnalités des sociétés. Et une personnalité peut avoir plusieurs fois la même société de liée car une liaison personnalité - société comporte une date de début et une date de fin et un statut (en cours, terminé etc.), du coup si je n'ai pas le GROUP BY et que j'affiche les personnalité de telle société, ça va me sortir plusieurs fois la même personnalité.

  6. #6
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par jonjojo Voir le message
    Merci de la réponse. En fait en postant ici, j'ai volontairement simplifié ma requête; en vrai je faisais un SELECT p.id, p.nom etc.

    En fait le GROUP BY est utile car j'ai aussi de lié aux personnalités des sociétés. Et une personnalité peut avoir plusieurs fois la même société de liée car une liaison personnalité - société comporte une date de début et une date de fin et un statut (en cours, terminé etc.), du coup si je n'ai pas le GROUP BY et que j'affiche les personnalité de telle société, ça va me sortir plusieurs fois la même personnalité.
    En ce cas il faut utiliser DISTINCTet non GROUP BYpuisque vous n'utilisez aucune fonction d'agrégation (COUNT, AVG, MAX..), ni filtre sur une valeur agrégée (HAVING)

    De plus, comme dans votre requête initiale, vous faites une equi-jointure avec la tables des pays, en filtrant sur l'identifiant PK du pays, il n'y a aucune chance d'avoir plusieurs lignes ...

  7. #7
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 614
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 614
    Points : 9 351
    Points
    9 351

    Par défaut

    en l’occurrence, je dirai même qu'il faut filtrer, pour ne garder que les profils "actif" et ainsi ne plus avoir de doublons.

  8. #8
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 996
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 996
    Points : 6 582
    Points
    6 582
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par aieeeuuuuu Voir le message
    en l’occurrence, je dirai même qu'il faut filtrer, pour ne garder que les profils "actif" et ainsi ne plus avoir de doublons.
    il ne peut pas y en avoir car :


    Citation Envoyé par jonjojo Voir le message
    personnalites
    - id
    - nom

    id =>PRIMARY KEYnom => INDEX

    personnalites_pays
    - id_personnalite
    - id_pays
    id_personnalite + id_pays => PRIMARY KEY

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT p.nom FROM personnalites AS p, personnalites_pays AS pp WHERE p.id = pp.id_personnalite AND pp.id_pays = 1 GROUP BY p.id ORDER BY p.nom ASC LIMIT 50000,50
    Donc le groupage est non seulement incohérent avec le SELECT (spécificité MySQL) mais surtout inutile
    Et la jointure n'est pas normalisée

  9. #9
    Futur Membre du Club
    Profil pro
    Inscrit en
    octobre 2012
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2012
    Messages : 11
    Points : 5
    Points
    5

    Par défaut

    Merci de la réponse, j'ai donc :

    Remplacé le GROUP BY par DISTINCT partout ou j'ai pas de COUNT, SUM etc.
    Et surtout utilisé le DISTINCT que lorsque j'en ai besoin.
    Si on filtre uniquement par pays, je n'ai plus le DISTINCT donc, car une personnalité ne peut pas avoir plusieurs fois le même pays de lié.

    Comme je disais ma requête est celle d'un moteur de recherche personnalité. Il est possible de filtrer par une dizaines de filtres, comme le pays, les genres, les sociétés liées, l'âge, le statut d'activité, etc.) du coup il peut y avoir des jointures avec 6 tables différentes au max. Certaines ont besoin d'un GROUP BY (afficher les personnalités qui ont le genre "Musicien" et "Acteur" par exemple)

    SELECT p.nom FROM personnalite AS p, personnalite_genre AS pg WHERE p.id = pg.id_personnalite AND pg.id_genre IN (1,2) GROUP BY p.id HAVING COUNT(id_genre) = 2 ORDER BY p.nom ASC LIMIT 0,50
    Du coup la requête peut être bien plus compliquée. Je génèré donc dynamiquement la requête en fonction des filtres de recherche demandés.

    Au niveau des profils "actifs" j'ai pas bien saisie mais je suis obligé de mettre un DISTINCT si je fais une jointure avec ma table personnalites_societes par exemple car comme je dis plus haut, une personnalité peut être liée plusieurs fois à la même sociétée et elle ressort donc plusieurs fois sans le DISTINCT.

    Après niveau performances, le DISTINCT c'est pas mieux que le GROUP BY.

    SELECT DISTINCT(p.id), p.nom FROM personnalites AS p, personnalites_societes AS ps WHERE p.id = ps.id_personnalite AND ps.id_societe = 1 ORDER BY p.nom ASC LIMIT 0,50

  10. #10
    Expert confirmé
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 623
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 623
    Points : 5 059
    Points
    5 059

    Par défaut

    Si les seuls données à afficher sont celles en provenance de la table personnalites alors il est probablement possible de modifier la génération de requête en faisant des tests d'existance plutôt que des jointures puis DISTINCT :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT p.nom 
      FROM personnalites AS p
     where 1 = 1
       and exists (select 1
                     from personnalites_pays AS pp 
                    WHERE p.id = pp.id_personnalite 
                      AND pp.id_pays = 1)
     ORDER BY p.nom ASC 
     LIMIT 50000,50
    En testant sur un ou 2 critères identifiés comme lents, ça doit pouvoir permettre de valider ou non le gain.

    PS : Le 1 = 1 c'est pour initialiser la génération de la requête, après il suffit d'ajouter des AND EXISTS en fonction des critères plus facilement.

  11. #11
    Futur Membre du Club
    Profil pro
    Inscrit en
    octobre 2012
    Messages
    11
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2012
    Messages : 11
    Points : 5
    Points
    5

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    Si les seuls données à afficher sont celles en provenance de la table personnalites
    Merci de la réponse. Malheureusement j'affiche aussi la nationalité.

    Par curiosité j'ai fait des tests et ça aurait été mieux, je passe de 0.6s à 0.5s !

  12. #12
    Expert éminent Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    2 976
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 77
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 2 976
    Points : 9 073
    Points
    9 073

    Par défaut

    Salut jonjojo.

    Citation Envoyé par jonjojo
    Optimiser un bête SELECT qui mets plus de 2 secondes
    2 secondes, ce n'est pas la fin du monde. En général, le temps moyen d'une requête doit être inférieur à 1 seconde.
    Je suppose que vous perdez plus de temps à vous décider si vous allez lancer la requête ou pas.

    Citation Envoyé par jonjojo
    Mes deux tables sont en MyISAM.
    Normalement cela devrait être plus rapide qu'avec le moteur InnoDB.

    Citation Envoyé par Escartefigue
    Et la jointure n'est pas normalisée
    Tout à fait.

    @ jonjojo : vous devriez utiliser les jointures, comme ci-après :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT    distinct t1.nom, ...
    
          FROM  personnalites      AS t1
    
    INNER JOIN  personnalites_pays AS t2
            ON  t2.id_personnalite = t1.id
           AND  t2.id_pays         = 1
    
      ORDER BY  t1.nom ASC
         LIMIT  50000,50
    En ce qui concerne le "group by", je suis d'accord qu'il ne sert à rien. Vous pouvez le remplacer par le "distinct" si cela est vraiment nécessaire.

    Comme vous faites une jointure entre vos deux tables, je ne voie pas si vous avez utilisez les clef étrangères.
    Autrement dit, "id_personnalite" doit être déclaré ainsi :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CONSTRAINT `FK_PERSONNALITES` FOREIGN KEY (`id_personnalite`) REFERENCES `personnalites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    Pour vos index, je ne voie aucun problème, même sur la primary key de la table "personnalites_pays".
    Je voie que vous faites un "order by" sur la colonne "nom" de la table "personnalites".
    Pour éviter de trier, il y a une possibilité qui peut améliorer la performance de votre requête, mais peut dégrader les autres requêtes.
    Il s'agit d'utiliser la "primary key", de la façon suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    primary key nom, id
    unique index id
    Après modification de vos deux index ci-dessus, il faudra réorganiser votre table en faisant :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    alter table `personnalites` order by `nom`;
    Trier une table qui est déjà dans l'ordre du tri vous fait gagner un peu de temps.
    Le "order by" dans ce cas là n'est plus nécessaire. A vous de tester !

    Est-ce que vous avez gagner quelques millisecondes ?

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

Discussions similaires

  1. optimiser une reqête qui mets beaucoup de temps
    Par rickways dans le forum Requêtes
    Réponses: 6
    Dernier message: 10/09/2009, 21h10
  2. Réponses: 6
    Dernier message: 04/07/2009, 22h41
  3. session_start qui met plus d'une seconde
    Par Sephiroth Lune dans le forum Sessions
    Réponses: 4
    Dernier message: 19/09/2008, 17h46
  4. un SELECT qui retourne plus de ligne que la table
    Par gomodo dans le forum Langage SQL
    Réponses: 6
    Dernier message: 29/10/2007, 15h17
  5. [C# 2.0] Application qui ne se met plus en Minimize
    Par Jérôme Lambert dans le forum Windows Mobile
    Réponses: 1
    Dernier message: 29/09/2006, 11h34

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