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 :

GROUP BY suivit d'un ORDER BY ne fait pas bon ménage


Sujet :

Requêtes MySQL

  1. #1
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Avril 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2012
    Messages : 18
    Points : 39
    Points
    39
    Par défaut GROUP BY suivit d'un ORDER BY ne fait pas bon ménage
    Bonjour

    Je suis actuellement en train de mettre en place une requête pour récupérer les sociétés les plus visitées du site dans une catégorie donnée.

    Voila le schema de la table company

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    CREATE TABLE `company` (
      `id` int(11) NOT NULL,
      `category_id` int(11) DEFAULT NULL,
      `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `nb_visits` int(11) NOT NULL
    )
    Pour pouvoir récupérer les 10 sociétés les plus visitées pour une catégorie donnée c'est assez simple, il suffit de faire la requête suivante

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT *
    FROM company
    WHERE category_id = 1 ;; par exemple
    ORDER BY nb_visits DESC
    LIMIT 10
    Le problème c'est que dans la base, il y a des plusieurs sociétés différentes ayant le même nom, et on veut dans notre requête récupérer 10 sociétés les plus visitées, mais ayant un nom différent.
    Pour ça je fait un simple group by name de cette façon

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    SELECT *
    FROM company
    WHERE category_id = 1 ;; par exemple
    GROUP BY name
    ORDER BY nb_visits DESC
    LIMIT 10
    Si j'ai par exemple une société A avec un nb_visits à 1000 et une autre société A avec un nb_visits à 2, le comportement du group by est indéterminé, il va parfois gardé la société A avec 1000, parfois la société A avec 2.
    Dans le cas ou il garde la société A avec 1000, cela m'arrange card pour le ORDER BY, 1000 sera bien pris en compte. Par contre dans le cas ou il garde la société A avec 2, c'est embêtant car du coup le plus gros chiffre (1000) ne sera pas pris en compte dans le ORDER BY, et la société A, qui est potentiellement la plus visité du site, n'apparaîtra pas dans la requête.

    Une idée de comment résoudre la problématique.

  2. #2
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Pour résoudre votre problème, respectez la norme SQL dans l'écriture de vos requête (si MySQL est un mauvais élève, vous n'êtes pas obligé de faire les mêmes erreurs).

    La norme SQL indique :
    - GROUP BY doit indiquer l'ensemble des colonnes du SELECT qui ne font pas partie d'un aggrégat

    Par conséquent, SELECT * avec GROUP BY name ne repespecte pas la norme, et le comportement de MySQL est absolument impossible à prévoir (en tout bon SGBD qu'il devrait être, il devrait planter lamentablement avec quelques messages d'insulte, mais ce n'est malheureusement pas le cas, il préfère faire n'importe quoi dans son coin).

    Réécrivez donc vos clauses SELECT et GROUP BY de façon à respecter la norme, et le résultat devrait s'approcher de ce que vous désirez.

    Ca devrait donner un truc du genre (en partant du principe qu'il faut cumuler les visites des sociétés homonymes) :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    select name, sum(nb_visits) total_visits
    from Compagny
    where category_id = 1
    group by name
    order by total_visits desc
    LIMIT 10
    On ne jouit bien que de ce qu’on partage.

  3. #3
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Avril 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2012
    Messages : 18
    Points : 39
    Points
    39
    Par défaut
    Je vous remercie de m'avoir aidé. C'est déjà beaucoup plus claire pour moi.

    Par contre la solution proposée ne me convient pas, puisque je ne veux pas cumuler toutes les vues, mais je vais seulement récupérer celui qui en a le plus (parmi ceux qui ont le même nom), et aussi récupérer son identifiant.

    Encore merci.

  4. #4
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Il suffit donc, dans mon exemple, de remplacer le SUM(), qui fait le cumul, par un MAX(), qui retient la ligne ayant la plus grande valeur.
    On ne jouit bien que de ce qu’on partage.

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Avril 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2012
    Messages : 18
    Points : 39
    Points
    39
    Par défaut
    Et pour récupérer leur id sachant que je ne peux pas le mettre dans le select sinon le group by ne respectera pas la norme, et je ne peux pas le mettre dans le group by sinon car ça n'aura pas le bon comportement.

  6. #6
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Dans ce cas, c'est plus compliqué.

    On peut partir sur une sous-requête, avec une auto-jointure sur le nombre de visites, mais ça va être sale et contre-performant.

    Donc on va prendre le problème autrement.

    Pour un même tuple "name, category_id", quel est le magasin qui a le plus de visite ?
    -> Celui qui a le MAX(nb_visits)
    -> Ou bien celui qui n'a pas de tuple avec un nb_visits suppérieur

    Ce qui donne la requête suivante :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    select c1.id, c1.name, c1.category_id, c1.nb_visits
    from company c1
    left outer join company c2 on c2.category_id = c1.category_id and c2.nom = c1.nom and c2.nb_visits > c1.nb_visits
    where c1.category_id = 1
    and c2.id is null -- c'est à dire les lignes de c1 pour lesquelles il n'y a pas de magasin avec plus de visites
    order by c1.nb_visits desc
    limit 10
    On ne jouit bien que de ce qu’on partage.

  7. #7
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut à tous.

    Citation Envoyé par StringBuilder
    Pour résoudre votre problème, respectez la norme SQL dans l'écriture de vos requête
    La norme n'est pas tout ! Il faudrait avoir un peu la maîtrise du langage sql, même s'il s'agit de 'mysql'.

    Citation Envoyé par StringBuilder
    Il suffit donc, dans mon exemple, de remplacer le SUM(), qui fait le cumul, par un MAX(), qui retient la ligne ayant la plus grande valeur.
    C'est exactement ce qu'il ne faut pas faire ! le max() ne retient pas la ligne, mais sélectionne la plus haute valeur de la colonne.
    Et en plus vous ne répondez pas à la question posée.

    Citation Envoyé par jojolebg
    Par contre la solution proposée ne me convient pas, puisque je ne veux pas cumuler toutes les vues, mais je vais seulement récupérer celui qui en a le plus (parmi ceux qui ont le même nom), et aussi récupérer son identifiant.
    jojolebg demande comment sélectionner, pour un ensemble de 'name' identique (cas des doublons), la plus grande valeur du nombre de visiteurs.

    Le 'group by' n'est pas la solution car il n'y a pas de regroupement à faire.
    Il suffit de sélectionner la ligne ayant la plus grande valeur en nombre de visiteurs, pour un 'name' donné. Les autres lignes ne seront pas sélectionnées.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select id, nb_visits
    from Compagny as tb1
    where nb_visits = (
    	select max(nb_visits)
    	from Company as tb2
    	where tb2.name = tb1.name)
    and category_id = 1
    order by nb_visits desc
    LIMIT 10
    Citation Envoyé par StringBuilder
    On peut partir sur une sous-requête, avec une auto-jointure sur le nombre de visites, mais ça va être sale et contre-performant.
    Qu'est-ce qui va être contre-performant ? Si vous parlez de votre solution, oui, je confirme que ce n'est pas ainsi que l'on procède.

    Avant tout chose, il faut répondre à la problématique posée. Ensuite, on peut aborder la question de la performance.
    Pour améliorer les performances, il suffit d'ajouter un index sur la colonne 'name'.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    alter table Company add index `name_idx` (`name`);
    Et voici un petit exemple pour illustrer la demande de jojolebg :

    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
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    (
      `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `name`     char(20)         NOT NULL,
      `visiteur` integer unsigned NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    INSERT INTO `test` (`name`, `visiteur`) VALUES
    ('un',    25),
    ('un',    163),
    ('un',    458),
    ('un',    13),
    ('deux',  13),
    ('trois', 33),
    ('trois', 75)
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  1 | un    |       25 |
    |  2 | un    |      163 |
    |  3 | un    |      458 |
    |  4 | un    |       13 |
    |  5 | deux  |       13 |
    |  6 | trois |       33 |
    |  7 | trois |       75 |
    +----+-------+----------+
    --------------
    select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  3 | un    |      458 |
    |  7 | trois |       75 |
    |  5 | deux  |       13 |
    +----+-------+----------+
    --------------
    explain select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+
    | id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
    +----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+
    |  1 | PRIMARY            | tb1   | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using filesort |
    |  2 | DEPENDENT SUBQUERY | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where                 |
    +----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+
    --------------
    alter table test add index `name_idx` (`name`)
    --------------
     
    --------------
    explain select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+--------------------+-------+------+---------------+----------+---------+---------------+------+-----------------------------+
    | id | select_type        | table | type | possible_keys | key      | key_len | ref           | rows | Extra                       |
    +----+--------------------+-------+------+---------------+----------+---------+---------------+------+-----------------------------+
    |  1 | PRIMARY            | tb1   | ALL  | NULL          | NULL     | NULL    | NULL          |    7 | Using where; Using filesort |
    |  2 | DEPENDENT SUBQUERY | tb2   | ref  | name_idx      | name_idx | 20      | base.tb1.name |    1 | NULL                        |
    +----+--------------------+-------+------+---------------+----------+---------+---------------+------+-----------------------------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
     
    Appuyez sur une touche pour continuer...
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  8. #8
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par Artemus24 Voir le message
    Salut à tous.
    Bonjour, que d'animosité !

    Citation Envoyé par Artemus24 Voir le message
    C'est exactement ce qu'il ne faut pas faire ! le max() ne retient pas la ligne, mais sélectionne la plus haute valeur de la colonne.
    Et en plus vous ne répondez pas à la question posée.

    jojolebg demande comment sélectionner, pour un ensemble de 'name' identique (cas des doublons), la plus grande valeur du nombre de visiteurs.

    Le 'group by' n'est pas la solution car il n'y a pas de regroupement à faire.
    Ah oui ?
    Dans la question d'origine, il n'est pas précisé qu'il faut les ID dans la réponse.
    Cette requête rapporte donc exactement le bon résultat, au détail près qu'il ne ramène que les couples nom/nb_visites, sans l'ID, avec un GOUPY BY, sans sous-requête ni jointure.
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    select name, sum(nb_visits) total_visits
    from Compagny
    where category_id = 1
    group by name
    order by total_visits desc
    LIMIT 10

    Citation Envoyé par Artemus24 Voir le message
    Il suffit de sélectionner la ligne ayant la plus grande valeur en nombre de visiteurs, pour un 'name' donné. Les autres lignes ne seront pas sélectionnées.

    Qu'est-ce qui va être contre-performant ?

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select id, nb_visits
    from Compagny as tb1
    where nb_visits = (
    	select max(nb_visits)
    	from Company as tb2
    	where tb2.name = tb1.name)
    and category_id = 1
    order by nb_visits desc
    LIMIT 10
    Oui, avec une sous-requête, c'est à dire quel que chose de contre-performant, qu'on doit tenter au maximum d'éviter : pour chaque ligne de résultat, MySQL doit effectuer la sous-requête.

    Citation Envoyé par Artemus24 Voir le message
    Si vous parlez de votre solution, oui, je confirme que ce n'est pas ainsi que l'on procède.

    Avant tout chose, il faut répondre à la problématique posée. Ensuite, on peut aborder la question de la performance.
    Avez-vous au moins testé ma requête ? Benckmarkée ? (sur quelques milliers de lignes plutôt que 10 pauvres lignes ?

    Citation Envoyé par Artemus24 Voir le message
    Pour améliorer les performances, il suffit d'ajouter un index sur la colonne 'name'.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    alter table Company add index `name_idx` (`name`);
    Ah, moi j'aurais créé un index composé de category_id, name et nb_visits, mais bon, si vous préférez que MySQL soit obligé de faire un RANGE SCAN au lieu d'un INDEX SEEK...
    On ne jouit bien que de ce qu’on partage.

  9. #9
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut à StringBuilder.

    Citation Envoyé par StringBuilder
    Bonjour, que d'animosité !
    Où voyez-vous de l'animosité ?
    Je suis peut-être brusque dans ma façon de m'exprimer mais en aucune façon, je ne cherche à rabaisser mon interlocuteur.
    Tout au contraire, l'opinion des autres m'intéresse afin de me perfectionner !
    Si je me suis permis d'intervenir, c'est que votre solution, bien que produisant le résultat recherché, n'est pas conforme à la norme sql, ni à la performance.

    Citation Envoyé par jojolebg
    Par contre la solution proposée ne me convient pas, puisque je ne veux pas cumuler toutes les vues, mais je vais seulement récupérer celui qui en a le plus (parmi ceux qui ont le même nom), et aussi récupérer son identifiant.
    Dès le premier message, j'ai compris que 'jojolebg' recherche la valeur maximale pour un 'name' donné. Autrement dit, en cas de doublon, il veut récupérer qu'une seule ligne, celle contenant la valeur maximale.
    La solution que vous proposez est de cumuler toutes les lignes ayant le même 'name'. Ce n'est pas ce que 'jojolebg' recherche.
    Dans le seconde message de 'jojolebg', il confirme bien cela !

    Citation Envoyé par StringBuilder
    Oui, avec une sous-requête, c'est à dire quel que chose de contre-performant, qu'on doit tenter au maximum d'éviter : pour chaque ligne de résultat, MySQL doit effectuer la sous-requête.
    Je ne sais pas qui vous a dit que faire une sous-requête est contre-performante, mais son usage correspond exactement à la demande de 'jojolebg'.
    Vous proposer de faire une jointure. Or vous n'utilisez pas les colonnes de cette seconde à l'affichage.
    Donc inutile de procéder ainsi. Et de plus, cela ne correspond pas à la norme sql, puisque vous y êtes tant attaché. Autant donner de bon conseil, non ?
    La sous-requête correspond à ce genre de sélection ! Et je fais en sorte de respecter cette norme sql.

    J'ai repris votre solution que j'ai adapté à mon exemple précédent. Je me suis assuré d'avoir exactement le même résultat. Après tout, c'est ce que l'on attend !

    Je me suis attardé à faire un 'explain' et à analyser le compte-rendu de la colonne 'extra', pour comparer nos deux solutions.

    1) ici j'utilise mon index qui est uniquement sur la colonne 'name'.
    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
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    (
      `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `name`     char(20)         NOT NULL,
      `visiteur` integer unsigned NOT NULL,
      index `idx` (`name`)
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    INSERT INTO `test` (`name`, `visiteur`) VALUES
    ('un',    25),
    ('un',    163),
    ('un',    458),
    ('un',    13),
    ('deux',  13),
    ('trois', 33),
    ('trois', 75)
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  1 | un    |       25 |
    |  2 | un    |      163 |
    |  3 | un    |      458 |
    |  4 | un    |       13 |
    |  5 | deux  |       13 |
    |  6 | trois |       33 |
    |  7 | trois |       75 |
    +----+-------+----------+
    --------------
    select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  3 | un    |      458 |
    |  7 | trois |       75 |
    |  5 | deux  |       13 |
    +----+-------+----------+
    --------------
    select c1.id, c1.name, c1.visiteur
    from test c1
    left outer join test c2
    on  c2.name      = c1.name
    and c2.visiteur  > c1.visiteur
    where c2.id is null
    order by c1.visiteur desc
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  3 | un    |      458 |
    |  7 | trois |       75 |
    |  5 | deux  |       13 |
    +----+-------+----------+
    --------------
    explain
    select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+--------------------+-------+------+---------------+------+---------+---------------+------+-----------------------------+
    | id | select_type        | table | type | possible_keys | key  | key_len | ref           | rows | Extra                       |
    +----+--------------------+-------+------+---------------+------+---------+---------------+------+-----------------------------+
    |  1 | PRIMARY            | tb1   | ALL  | NULL          | NULL | NULL    | NULL          |    8 | Using where; Using filesort |
    |  2 | DEPENDENT SUBQUERY | tb2   | ref  | idx           | idx  | 20      | base.tb1.name |    2 | NULL                        |
    +----+--------------------+-------+------+---------------+------+---------+---------------+------+-----------------------------+
    --------------
    explain
    select c1.id, c1.name, c1.visiteur
    from test c1
    left outer join test c2
    on  c2.name      = c1.name
    and c2.visiteur  > c1.visiteur
    where c2.id is null
    order by c1.visiteur desc
    --------------
     
    +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
    +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
    |  1 | SIMPLE      | c1    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using temporary; Using filesort                                |
    |  1 | SIMPLE      | c2    | ALL  | idx           | NULL | NULL    | NULL |    8 | Using where; Not exists; Using join buffer (Block Nested Loop) |
    +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
     
    Appuyez sur une touche pour continuer...
    J'ai deux étapes, à savoir 'using where' et using filsort'. Le 'null' correspond à une étpae où l'on ne fait rien. Je ne l'ai pas comptabilisé en tant que telle.
    Tandis que vous en avez cinq étapes. Soit trois étapes de plus que ma solution.

    Dans la seconde solution, j'adapte l'index afin d'obtenir chez vous un meilleur résultat.
    Je mets 'name', 'visiteur' et 'id'. Pour la catégorie, je l'ai, comme vous avez dû le remarquer, supprimé de la table.

    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
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    (
      `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `name`     char(20)         NOT NULL,
      `visiteur` integer unsigned NOT NULL,
      index `idx` (`name`,`visiteur`,`id`)
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    INSERT INTO `test` (`name`, `visiteur`) VALUES
    ('un',    25),
    ('un',    163),
    ('un',    458),
    ('un',    13),
    ('deux',  13),
    ('trois', 33),
    ('trois', 75)
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  5 | deux  |       13 |
    |  6 | trois |       33 |
    |  7 | trois |       75 |
    |  4 | un    |       13 |
    |  1 | un    |       25 |
    |  2 | un    |      163 |
    |  3 | un    |      458 |
    +----+-------+----------+
    --------------
    select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  3 | un    |      458 |
    |  7 | trois |       75 |
    |  5 | deux  |       13 |
    +----+-------+----------+
    --------------
    select c1.id, c1.name, c1.visiteur
    from test c1
    left outer join test c2
    on  c2.name      = c1.name
    and c2.visiteur  > c1.visiteur
    where c2.id is null
    order by c1.visiteur desc
    --------------
     
    +----+-------+----------+
    | id | name  | visiteur |
    +----+-------+----------+
    |  3 | un    |      458 |
    |  7 | trois |       75 |
    |  5 | deux  |       13 |
    +----+-------+----------+
    --------------
    explain
    select *
    from test as tb1
    where visiteur = (
            select max(visiteur)
            from test as tb2
            where tb2.name = tb1.name
    )
    order by visiteur desc
    --------------
     
    +----+--------------------+-------+-------+---------------+------+---------+---------------+------+------------------------------------------+
    | id | select_type        | table | type  | possible_keys | key  | key_len | ref           | rows | Extra                                    |
    +----+--------------------+-------+-------+---------------+------+---------+---------------+------+------------------------------------------+
    |  1 | PRIMARY            | tb1   | index | NULL          | idx  | 28      | NULL          |    8 | Using where; Using index; Using filesort |
    |  2 | DEPENDENT SUBQUERY | tb2   | ref   | idx           | idx  | 20      | base.tb1.name |    4 | Using index                              |
    +----+--------------------+-------+-------+---------------+------+---------+---------------+------+------------------------------------------+
    --------------
    explain
    select c1.id, c1.name, c1.visiteur
    from test c1
    left outer join test c2
    on  c2.name      = c1.name
    and c2.visiteur  > c1.visiteur
    where c2.id is null
    order by c1.visiteur desc
    --------------
     
    +----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref          | rows | Extra                                |
    +----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------------------+
    |  1 | SIMPLE      | c1    | index | NULL          | idx  | 28      | NULL         |    8 | Using index; Using filesort          |
    |  1 | SIMPLE      | c2    | ref   | idx           | idx  | 20      | base.c1.name |    4 | Using where; Not exists; Using index |
    +----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------------------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
     
    Appuyez sur une touche pour continuer...
    Dans ce second exemple, nous lisons exactement le même nombre de lignes, soit 8+4 = 12 lignes.
    Mais vous avez une étape supplémentaire, celle de 'not exists' dont je n'ai pas besoin.

    Si l'on compare maintenant les deux tests, vous constaterez que ma solution avec mon index (premier test), est plus performantes que votre solution et ce, dans les deux test.
    Je lis 8+2, soit 10 lignes, alors que vous en lisez 8+8, soit 16 lignes dans le premier test, ou soit 8+4, soit 12 lignes dans le second test.

    D'autre part, vous remarquerez que dans la deuxième phase (soit 'depend subquery' ou chez vous 'simple', vous faites bien plus d'étape que chez moi.
    Faisons une multiplication entre les deux phases, pour comptabiliser le nombre d'étape.
    --> ma solution : 3X1 = 3
    --> votre solution : 2 X 3 = 6.
    En gros, votre solution dure deux fois plus longtemps que ma solution dans ce second test.

    Inversement, si je compare dans le premier test, la multiplication, je trouve :
    --> ma solution : 2X1 = 2.
    --> votre solution : 2X3 = 6.
    Soit trois fois plus long que ma solution.

    Inutile de faire un test plus conséquent, alors que ce petit exemple exprime nettement une différence dans les deux approches.
    Si vous avez mieux à me proposer, je suis preneur.

    Je pense que votre problème, c'est que vous ne savez pas interpréter un 'explain'.

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

  10. #10
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par Artemus24 Voir le message
    Dès le premier message, j'ai compris que 'jojolebg' recherche la valeur maximale pour un 'name' donné. Autrement dit, en cas de doublon, il veut récupérer qu'une seule ligne, celle contenant la valeur maximale.
    La solution que vous proposez est de cumuler toutes les lignes ayant le même 'name'. Ce n'est pas ce que 'jojolebg' recherche.
    Dans le seconde message de 'jojolebg', il confirme bien cela !
    Et c'est bien pour ça que je lui ai immédiatement proposé de remplacer le "SUM" par un "MAX" qui répondait exactement à sa première question, au détail près qu'on n'a pas l'ID dans le résultat.

    Citation Envoyé par Artemus24 Voir le message
    Je ne sais pas qui vous a dit que faire une sous-requête est contre-performante, mais son usage correspond exactement à la demande de 'jojolebg'.
    Vous proposer de faire une jointure. Or vous n'utilisez pas les colonnes de cette seconde à l'affichage.
    Donc inutile de procéder ainsi. Et de plus, cela ne correspond pas à la norme sql, puisque vous y êtes tant attaché. Autant donner de bon conseil, non ?
    La sous-requête correspond à ce genre de sélection ! Et je fais en sorte de respecter cette norme sql.
    Vous faites une sous-requête. Affichez-vous les données de cette dernière ? Non.
    En revanche, dans votre sous-requête, vous faites une opération de regroupement (MAX), ce dont se passe ma requête.
    Je regrette, mais :
    - Une sous-requête, au mieux, le SGBD va la convertir, en interne, en jointure. C'est à dire que l'optimiseur, si tout se passe bien, va produire à peu de chose près ma requête. Dans le pire des cas, il ne va pas y arriver et devoir traiter ligne à ligne la sous-requête. Ce qui veut dire, pour une table de 1000 lignes, 1001 requêtes, dont 1000 coûteuses qui effectuent un agrégat.
    - En revanche, une jointure (externe dans notre cas) va se traduire par une unique lecture de la table. Donc une unique requête, avec deux lectures de la même table, absolument rien de plus. Et qui plus est, aucune agrégation à faire.

    Enfin, les jointures, y compris externes sont présentes dans la norme SQL depuis ses premiers balbituements. Tous les SGBD, sans aucune exception, les supportent depuis au moins 30 ans.
    En revanche, pour les sous-requêtes, ça se corse, vers Ajaccio je crois. Elles n'étaient pas présentes dans la norme à ses débuts, et fin 90, pour ainsi dire aucun SGBD ne les supportait !*

    Alors j'ai un peu de mal à cerner à quel moment ma solution ne respecte pas la norme, bien au contraire.

    Si je ne m'abuse, la jointure externe est parfaitement présente dans la norme SQL-86, alors que la sous-requête n'apparaît que dans la norme SQL-92

    Citation Envoyé par Artemus24 Voir le message
    Je pense que votre problème, c'est que vous ne savez pas interpréter un 'explain'.
    J'ai l'impression que vous avez le problème inverse.
    D'autant qu'il manque une notion de coût dans votre explain plan, surtout sur un volume aussi faible (de nombreux SGBD ferons un simple full scan sur un tel volume de données... plus rapide de parcourir un unique objet de quelques dizaines d'octets que de devoir lire de multiples objets pas forcément plus petits !)
    On ne jouit bien que de ce qu’on partage.

  11. #11
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Avril 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2012
    Messages : 18
    Points : 39
    Points
    39
    Par défaut
    Merci à Artemus24 et StringBuilder de m'avoir aider.
    J'ai fait quelques tests sur mes données (environs 90 000 sociétés dans la base).

    Même si la solution de Artemus24 à base de sous requête m'a l'air plus intuitive, c'est pas celle qui carbure le plus.

    À titre de comparaison:
    La requête suivante (basée sur la solution d'Artemus24) met en moyenne 0,1532 secondes pour s'exécuter.
    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
    SELECT
      id, name, nbVisits
    FROM 
      company c
    WHERE 
      c.nbVisits = (SELECT MAX(ic.nbVisits)
               FROM company ic
               WHERE ic.name = c.name AND ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND c.country = 'TG')
      AND c.category_id IN (175) 
      AND c.online = 1 
      AND c.valid = 1 
      AND c.country = 'TG'
    ORDER BY 
      c.nbVisits DESC 
    LIMIT 5
    Tandis que la requête suivante (basée sur la solution de StringBuilder) met en moyenne 0,0623 secondes pour s'exécuter.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT
      c.id, c.name, c.nbVisits
    FROM
      company c
    LEFT OUTER JOIN
      company ic ON ic.category_id IN (175) AND ic.country = 'TG' AND ic.online = 1 AND ic.valid = 1 AND ic.name = c.name AND ic.nbVisits > c.nbVisits
    WHERE c.category_id IN (175)
      AND ic.id IS NULL
      AND c.country = 'TG'
      AND c.online = 1
      AND c.valid = 1
    ORDER BY
      c.nbVisits DESC
    limit 5

  12. #12
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Juste pour la petite blague, n'ayant pas de MySQL sous la main, j'ai fait des tests avec SQL Server 2014.

    Et contre toute attente, la sous-requête, qui pourtant, par expérience, devrait être plus lente... se trouve 1000 fois plus rapide que la jointure externe.

    MySQL ne semble cependant pas posséder les mécanismes internes permettant d'optimiser de la sorte la sous-requête.

    Donc pas de réponse absolument universelle. Il faut connaître les deux méthodes, et tester en fonction de son environnement

    http://www.developpez.net/forums/d15...-sous-requete/
    On ne jouit bien que de ce qu’on partage.

  13. #13
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Bonjour à tous.

    Désolé si je suis absent depuis dimanche, mais j'ai dû amener d'urgence mon ordinateur chez mon revendeur favoris, car il chauffait anormalement.

    Citation Envoyé par StringBuilder
    Vous faites une sous-requête. Affichez-vous les données de cette dernière ? Non.
    Justement, si j'utilise une sous-requête, c'est parce que je n'ai pas besoin des colonnes qui y sont utilisées.
    Inversement, je j'ai besoin de sélectionner des colonnes, j'utilise dans ce cas une jointure.
    Je ne sais pas très bien ce que vous avez compris de ma remarque sur ce sujet.

    Citation Envoyé par StringBuilder
    En revanche, dans votre sous-requête, vous faites une opération de regroupement (MAX), ce dont se passe ma requête.
    Vous avez mal regardé ma requête, je ne fais aucun regroupement comme vous sembler le croire. Je tiens à préciser qu'un regroupement, c'est un 'group by'.
    Puis vous affirmer que votre requête ne fait pas de regroupement. Je vous rappelle que, comme moi, vous utilisez la fonction max().
    Donc je ne voie pas de différence majeur entre ma façon de faire et votre façon de faire.

    Citation Envoyé par StringBuilder
    - Une sous-requête, au mieux, le SGBD va la convertir, en interne, en jointure.
    Qui vous a dit que cela se passait toujours ainsi ?
    Cela dépend de la requête que vous faites et aussi du SGBDR que vous utilisez. Donc ne généraliser pas sans savoir.
    Et pour savoir, il faut faire des tests de performances ! C'est le seul critère qui soit pertient. Le reste, c'est que du bla bla bla.

    Citation Envoyé par StringBuilder
    En revanche, une jointure (externe dans notre cas) va se traduire par une unique lecture de la table.
    Pour moi, c'est une affirmation gratuite qui repose sur vos croyance.
    Et de quelle lecture parlez-vous ? D'une lecture physique ou d'une lecture logique ?
    Mais ne sachant pas comment va se comporter l'optimiseur de MySql, j'ignore si ce que vous dites est vrai ou pas.

    Citation Envoyé par StringBuilder
    Enfin, les jointures, y compris externes sont présentes dans la norme SQL depuis ses premiers balbutiements. Tous les SGBD, sans aucune exception, les supportent depuis au moins 30 ans.
    Faux !
    Vous confondez la norme que les énonces et la mise en oeuvre dans un SGBDR.
    Si je prends le cas de DB2 que je connais, la jointure, c'est-à-dire avec l'usage des mots 'inner join', 'left outer join', est apparu assez tardivement dans les années 96-98.
    Dans la littérature qui date des années 92-95, il n'en est fait nulle notion. Cela fait tout juste 20 ans que cela existe dans DB2. Comme MySql n'existait pas au début des années 1990, il est hors jeu sur cette question.

    Attention, je ne parle pas de la jointure dite classique, du genre :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select a.clef, a.col, b.clef, b.col
    from table1 as a, table2 as b
    where a.clef = b.clef;
    qui est en fait une produit cartésien avec un critère de sélection (ici la clef).

    Citation Envoyé par StringBuilder
    Elles n'étaient pas présentes dans la norme à ses débuts, et fin 90, pour ainsi dire aucun SGBD ne les supportait !
    Encore faux ! DB2 les supportait déjà au début des années 90.
    Je ne connais pas vos sources, mais vous devriez changer de livres.
    Comme je n'ai pas de scanner, je ne peux pas vous faire de copies des pages de mes livres consacrés à DB2 où l'on aborde ces questions.

    Citation Envoyé par StringBuilder
    Alors j'ai un peu de mal à cerner à quel moment ma solution ne respecte pas la norme, bien au contraire.
    En tant qu'écriture de la requête, vous n'enfreignez aucune norme, sinon vous auriez un beau 'syntax error'.
    Vous faites un mauvais usage de la fonction max().
    Pour sélectionner une ligne dont celle-ci contient, par exemple, le nombre maximal de visiteur, on recherche par une sous-requête corrélée, la valeur max que l'on vient égaliser à la requête principale.
    Ce que vous faites, c'est ni plus ni moins qu'un produit cartésien sur la même table. Vous lisez trop de lignes qui vont vous faire perdre du temps pour rien.

    Citation Envoyé par StringBuilder
    Si je ne m'abuse, la jointure externe est parfaitement présente dans la norme SQL-86, alors que la sous-requête n'apparaît que dans la norme SQL-92
    Depuis le départ je vous parle de performance, et vous me répondre norme. On n'arrivera pas à se comprendre.

    Citation Envoyé par StringBuilder
    J'ai l'impression que vous avez le problème inverse.
    C'est-à-dire ? C'est quoi le problème inverse ? J'utilise le explain pour argumenter ce que j'avance.

    Citation Envoyé par StringBuilder
    D'autant qu'il manque une notion de coût dans votre explain plan
    Le nombre de lectures que fait MySql ne vous satisfait pas. Je veux bien, mais que dois-je utiliser pour vous convaincre ?

    Je ne sais pas si c'est vous, mais je reçois -2 points, juste parce que j'essaye d'argumenter.
    Que dois-je comprendre ? Que vous préférez rester sur vos positions, plutôt que de chercher à comprendre ?

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

  14. #14
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Bonjour à tous.

    Citation Envoyé par jojolebg
    Même si la solution de Artemus24 à base de sous requête m'a l'air plus intuitive, c'est pas celle qui carbure le plus.
    Elle n'est pas du tout intuitive. On retrouve cette sous-requête corrélée dans la littérature consacrée à SQL.

    Ma recommandation, comme celle de StringBuilder ne se porte pas uniquement sur la réécriture de la requête, mais aussi sur l'usage d'un index.
    Dans vos tests, avez-vous créé un index ? Et si OUI lequel ? Et pour quelle requête ?

    Et comment avez-vous testé la performance de votre requête ?
    Admettons que vous faites mon test en premier puis celui de StringBuilder ensuite.
    Si vous n'avez pas dit explicitement que vous ne désirez pas stocker le résultat de la requête dans les buffers de MySql, au second passage, le temps d'exécution que vous obtiendrez sera celui de la lecture du résultat stockée dans les buffers et non la performance réelle du temps d'exécution de la requête.
    Et vu le temps que vous donnez, il me semble justement que nous nous trouvons dans le cas que je soulève. Autrement dit, ce résultat que vous nous indiquez ne veut rien dire.


    Citation Envoyé par StringBuilder
    Et contre toute attente, la sous-requête, qui pourtant, par expérience, devrait être plus lente... se trouve 1000 fois plus rapide que la jointure externe.
    Depuis le départ de cette conversation, je passe pour un imbécile qui ne sait pas de quoi il parle et tout d'un coup, comme par hasard, après vérification, vous reconnaissez que je n'ai pas tort.

    Citation Envoyé par StringBuilder
    Donc pas de réponse absolument universelle.
    Voilà maintenant une nouvelle croyance. Et bien non, cela n'existe pas des réponses universelles.
    Une requête comme un index doit s'adapter en fonction de la structure de vos tables, de la volumétrie et surtout du résultat que vous recherchez.
    Je ne fais pas la même requête sir je désire obtenir la totalité de mes résultats ou si je désire que les cinq premiers.
    Pour arriver à cela, une seule solution, faire des tests. Et quand on croit qu'une solution est dégradée, on est surpris de voir le temps nécessaire à son obtention.

    Je vous confirme qu'il n'existe aucune solution universelle, juste des cas particuliers qui peuvent changer d'un SGBDR à un autre ou encore d'une base de données à une autre.
    Bon, je ne désire pas exprimer qu'il n'y a que des cas particuliers, mais la vérité de ce que l'on cherche à obtenir se trouve quelque part entre le cas particulier et le cas général.

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

  15. #15
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Vous voila pris sur le fait par le mensonge....
    Au sujet des jointures INNER JOIN, OUTER... , vous affirmez :
    Citation Envoyé par Artemus24 Voir le message
    Dans la littérature qui date des années 92-95, il n'en est fait nulle notion. Cela fait tout juste 20 ans que cela existe dans DB2. Comme MySql n'existait pas au début des années 1990, il est hors jeu sur cette question.
    Et vous êtes pris sur le fait...
    Chris Date et Hugh Darwen dans le livre "A Guide to SQL Standard", première édition 1992
    http://www.amazon.com/Guide-SQL-Stan...dp_ob_image_bk
    Citent bien les jointures JOIN comme opération de jointure !
    Pour preuve de votre malhonnêteté intellectuelle voici une copie de la page 136... tout un chapitre étant consacré aux jointures normalisées !
    Nom : Date Darwen SQL JOIN.jpg
Affichages : 169
Taille : 200,8 Ko

    Vous avez pas de pot car je possède pratiquement tous les livres publiés sur le langage SQL.

    Vous voila mis le nez dans votre caca !

    Je vous rappelle que je vous avait proposé de vous envoyer gratuitement par la poste mon dernier livre sur SQL afin de parfaire votre culture visiblement déficiente ! Vous l'avez refusé sous des principes fallacieux... Il fallait donner votre adresse et vous ne vouliez pas. Je vous ait même proposer la poste restante, mais sans doute avez vous eu peur d'aller le retirer comme si c'était un livre porno !


    Je ne perdrais as mon temps à montrer tous les autres mensonges et inepties que vous affirmez sans arrêt, mais je considère dorénavant que vous êtes dangereux. En effet, quelques internautes risque de suivre vos pseudo "conseils" et pourrir leurs requêtes voire pire leur bases !

    Notez pour votre culture qu'il existe même un opérateur de nom UNION JOIN rarement utilisé ! Je laisse à votre sagacité le soin de trouver pourquoi ! Vous risqueriez dans votre recherche de tomber sr un de mes écrit à ce sujet...
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  16. #16
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut SQLPRO.

    Je vous parle des livres en français que je possède et que l'on trouvait dans les librairies à Paris, à cette époque là !
    La littérature était peu abondante sur ce sujet et très souvent en retard par rapport à la littérature anglo-saxonne. Donc en quoi est-ce un mensonge ?

    Et je ne vous parle pas d'un hypothétique livre qui en 1992, était accessible uniquement sur commande depuis les états-unis et complètement inconnu en France, toujours à la même époque.
    Et pour le recevoir, il fallait attendre entre quatre à six mois, après la commande et ce, depuis une librairie de langue anglaise.

    Si vous me parlez de l'internet grand public en France, il est apparu au environ de 1995, si j'ai bonne mémoire.
    Et comme je commence à vous connaitre, vous allez me répliquer que c'est faux.

    Vous voulez toujours me faire passer pour je ne sais quoi, vous êtes prêt à dénicher un livre, une information juste pour venir me contredire.
    Que votre expérience soit différente de la mienne, je veux bien. Mais en aucune façon, je suis un menteur.

    Citation Envoyé par SQLPRO
    Je vous rappelle que je vous avait proposé de vous envoyer gratuitement par la poste mon dernier livre sur SQL afin de parfaire votre culture visiblement déficiente ! Vous l'avez refusé sous des principes fallacieux... Il fallait donner votre adresse et vous ne vouliez pas. Je vous ait même proposer la poste restante, mais sans doute avez vous eu peur d'aller le retirer comme si c'était un livre porno !
    Il n'y a rien de fallacieux dans mon refus : je n'ai aucune confiance en vous. Je ne vous communiquerai pas mon nom et mon adresse personnel.
    Et à juste titre, vous vous complaisez à toujours obtenir le dernier mot, comme si vous seul, déteniez la vérité.

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

  17. #17
    Nouveau membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    Avril 2012
    Messages
    18
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2012
    Messages : 18
    Points : 39
    Points
    39
    Par défaut
    Citation Envoyé par Artemus24 Voir le message
    Bonjour à tous.
    Ma recommandation, comme celle de StringBuilder ne se porte pas uniquement sur la réécriture de la requête, mais aussi sur l'usage d'un index.
    Dans vos tests, avez-vous créé un index ? Et si OUI lequel ? Et pour quelle requête ?
    Voici les index:
    Nom index Colonne Cardinalité
    PRIMARY id 80574
    category category_id 485
    parent parent_id 4950
    city city 1466
    country country 463
    isClient isClient 2
    online online 2
    valid valid 2
    name name 79202
    IDX_4FBF094F12469DE25373C96662C0E8F9E32BEEA8C0735FF5E237E062D5B category_id, country, isClient, online, valid, name, city 1414, 7920, 9900, 9900, 9900, 79202, 79202
    nbVisits nbVisits 1320

    Voici la requête:
    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
     
    SELECT
      id, name, nbVisits
    FROM 
      company c
    WHERE 
      c.nbVisits = (SELECT MAX(ic.nbVisits)
               FROM company ic
               WHERE ic.name = c.name AND ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND c.country = 'TG')
      AND c.category_id IN (175) 
      AND c.online = 1 
      AND c.valid = 1 
      AND c.country = 'TG'
    ORDER BY 
      c.nbVisits DESC 
    LIMIT 5
    Et voici le explain:

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY c index_merge category,country,online,valid,IDX_4FBF094F12469DE25373C96662C0E8F9E32BEEA8C0735FF5E237E062D5B category,online,valid 5,1,1 NULL 210 Using intersect(category,online,valid); Using where; Using filesort
    2 DEPENDENT SUBQUERY ic ref category,online,valid,name,IDX_4FBF094F12469DE25373C96662C0E8F9E32BEEA8C0735FF5E237E062D5B name 767 goafrica_sf.c.name 1 Using where

    Et comment avez-vous testé la performance de votre requête ?
    Pour tester, j'ai exécuté la requête une 20 aines de fois sur phpmyadmin et j'ai fait la moyenne du temps d'exécution.


    Admettons que vous faites mon test en premier puis celui de StringBuilder ensuite.
    Si vous n'avez pas dit explicitement que vous ne désirez pas stocker le résultat de la requête dans les buffers de MySql, au second passage, le temps d'exécution que vous obtiendrez sera celui de la lecture du résultat stockée dans les buffers et non la performance réelle du temps d'exécution de la requête.
    Et vu le temps que vous donnez, il me semble justement que nous nous trouvons dans le cas que je soulève. Autrement dit, ce résultat que vous nous indiquez ne veut rien dire.
    Quelque soit l'ordre, que je fasse votre requête avant ou après celle de StringBuilder, l'ordre de grandeur reste le même.
    Sinon comment je peux faire pour mieux tester la performance de la requête.

  18. #18
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    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 : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Bonjour jojolebg.

    Si je suis intervenu dans ce sujet, ce n'est pas pour mettre le bordel ou rabaisser quiconque, mais pour montrer qu'il existe une autre solution, répondant à votre demande.
    Je sais très bien que la question de la performance est pour beaucoup d'utilisateurs complètement secondaire, à la condition que celle-ci s'exécute dans une durée de temps acceptable.
    Maintenant, vous êtes le seul à prendre la décision finale pour solutionner votre problème.

    Il y a une petite erreur dans votre requête.
    Dans la sous-requête corrélée, vous avez mis :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE ic.name = c.name AND ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND c.country = 'TG')
    Or il ne faut pas mettre "c.country" mais "ic.country".

    Citation Envoyé par jojolebg
    Voici les index:
    Je n'aime pas trop travailler dans le vide, c'est-à-dire donner des conseils sans pouvoir les expérimenter auparavant.

    Je reprends votre requête :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT  id, name, nbVisits
    FROM  company c
    WHERE c.nbVisits = (SELECT MAX(ic.nbVisits)
                        FROM company ic
                        WHERE ic.name = c.name AND ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG')
    AND c.category_id IN (175) 
    AND c.online      = 1 
    AND c.valid       = 1 
    AND c.country     = 'TG'
     
    ORDER BY c.nbVisits DESC 
    LIMIT 5
    Première remarque : à verifier.
    Quand vous faites un where 'c.nbVisits = (SELECT MAX(ic.nbVisits)', vous sélectionnez la ligne contenant la plus haute valeur maximale de la colonne nbVisits.
    Si vous utilisez plusieurs critères qui sont les mêmes dans la sous-requête et la requête, il y a sûrement une simplification à faire.

    La question que je me pose est celle à quoi se rapporte le nombre de visiteurs, puisque nous recherchons la plus haute valeur.
    Autrement dit, pour un même 'name', avons-nous toujours :
    --> online = 1
    --> valid = 1
    --> category_id = 175
    --> country = 'TG'.
    ou bien, il peut y avoir plusieurs valeurs possibles.

    Si la réponse est OUI, c'est-à-dire que les critères sont toujours identiques pour un name alors vous pouvez les supprimer car ils font un double emploi.

    Oui mais Lesquelles seront conservés ?
    Je pense que ce sont ceux de la sous-requête qu'il faut conserver. Et comme je le dis ci-dessus, c'est à vérifier.

    Inversement, si la réponse est NON, il ne faut pas changer votre requête.

    Ce n'est que du détail mais cela permet d'aléger la requête en lisibilité.

    Deuxième remarque :
    L'index qui se nomme 'IDX_4FBF094F12469DE25373C96662C0E8F9E32BEEA8C0735FF5E237E062D5B' n'a pas été utilisé dans la performance de la requête.
    Je pense que l'index que vous avez créé est inapproprié à votre requête.

    Pour l'index, il y a deux points à traiter, soit :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE ic.name = c.name AND ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND c.country = 'TG')
    et soit:
    Votre index se fait sur les colonnes : category_id, country, isClient, online, valid, name, city.
    Je ne voie nulle part dans votre requête la colonne isClient ?

    Sous toute réserve, je propose de faire l'index suivant :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    alter table company add index `idx_perf` (`name`,`online`,`valid`,`category_id`,`country`, `id`, `nbVisits`);
    En ce que concerne les tests, je ferrai ceci :
    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
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    explain SELECT SQL_NO_CACHE id, name, nbVisits
    FROM company c
    WHERE nbVisits = (SELECT MAX(ic.nbVisits)
               FROM company ic
               WHERE ic.name        = c.name
    		   AND   ic.online      = 1
    		   AND   ic.valid       = 1
    		   AND   ic.category_id IN (175)
    		   AND   ic.country     = 'TG')
    AND category_id IN (175) 
    AND online      = 1 
    AND valid       = 1 
    AND country     = 'TG'
    ORDER BY nbVisits DESC 
    LIMIT 5;
     
    set profiling=1;
    SELECT SQL_NO_CACHE SELECT SQL_NO_CACHE id, name, nbVisits
    FROM company c
    WHERE nbVisits = (SELECT MAX(ic.nbVisits)
               FROM company ic
               WHERE ic.name        = c.name
    		   AND   ic.online      = 1
    		   AND   ic.valid       = 1
    		   AND   ic.category_id IN (175)
    		   AND   ic.country     = 'TG')
    AND category_id IN (175) 
    AND online      = 1 
    AND valid       = 1 
    AND country     = 'TG'
    ORDER BY nbVisits DESC 
    LIMIT 5;
     
    SHOW PROFILES;
    SHOW PROFILE CPU FOR QUERY 1;
    set profiling=0;
    En mettant 'sql_no_cache', la requête ne sera pas stockée dans les buffers du serveur MySql.
    Il y a juste un léger problème, le temps d'exécution est juste approximatif.
    Ce qui est bizarre dans son fonctionnement, c'est que vous n'obtenez jamais le même résultat.
    On peut s'en servir comme ordre de grandeur.

    Que ce soit la solution de StringBuilder ou la mienne, les temps d'exécution sont assez semblables. La volumétrie que vous avez, 80574 lignes, n'est pas énorme.
    Les temps d'exécutions sont peu significatives pour départager les deux solutions proposées.

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

  19. #19
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par Artemus24 Voir le message
    Salut SQLPRO.

    Je vous parle des livres en français que je possède et que l'on trouvait dans les librairies à Paris, à cette époque là !
    La littérature était peu abondante sur ce sujet et très souvent en retard par rapport à la littérature anglo-saxonne. Donc en quoi est-ce un mensonge ?
    J'ai la légère impression que vous me prenez pour un con et que vous prenez les internautes qui vous lisent pour des imbéciles !

    En effet dans votre précédent message vous n'avez nullement indiqué la restriction de la langue française au sujet des livres sur SQL et il est évident qu'en domaines techniques et plus particulièrement en informatique celle-ci domine depuis la fin de la seconde guerre mondiale.

    De plus vous faites référence très souvent à DB2 dont la documentation est exclusivement en anglais...

    D’où mon interrogation...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  20. #20
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Vous battez pas hein :o

    Au final, y'a toujours "36 solutions pour arriver au même résultat en SQL, et y'a pas deux solutions qui ont des performances comparables d'un SGBD à l'autre" (j'aurais presque envie de rajouter "surtout quand on compare MySQL à des SGBD haut de gamme", mais on m'a dit qu'il ne fallait pas troller).

    Après, le principal, c'est que Jojolebg ait une réponse à sa question, et visiblement le choix entre deux solutions qui sont dans tous les cas plus rapides que sa tentative initiale, ce qui était le but rechercher.

    On est tous amis, bisounours tout ça.
    On ne jouit bien que de ce qu’on partage.

Discussions similaires

  1. group by 2 colonnes et order by une autre colonne
    Par _vivi_ dans le forum Requêtes
    Réponses: 3
    Dernier message: 17/03/2014, 13h31
  2. Réponses: 1
    Dernier message: 16/11/2011, 16h17
  3. Efficacité: GROUP BY plus rapide que ORDER BY ?
    Par Chekov dans le forum Langage SQL
    Réponses: 7
    Dernier message: 19/12/2007, 10h33
  4. [MySQL] Order by qui marche pas, Que faire ?
    Par mulbek dans le forum PHP & Base de données
    Réponses: 25
    Dernier message: 06/01/2006, 15h48
  5. Réponses: 10
    Dernier message: 30/11/2004, 11h12

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