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

Version imprimable

27/08/2015, 14h17
jojolebg

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:

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:

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:

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.
28/08/2015, 17h36
StringBuilder
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:

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
28/08/2015, 17h50
jojolebg

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.
28/08/2015, 17h57
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.
28/08/2015, 18h00
jojolebg

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.

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:

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

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:

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:

alter table Company add index `name_idx` (`name`);

Et voici un petit exemple pour illustrer la demande de jojolebg :

Code:

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...

30/08/2015, 10h35
StringBuilder
Citation:

Envoyé par Artemus24

Salut à tous.

Bonjour, que d'animosité !

Citation:

Envoyé par Artemus24

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:

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

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:

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

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

Pour améliorer les performances, il suffit d'ajouter un index sur la colonne 'name'.

Code:

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...

Salut à StringBuilder.

Citation:

Envoyé par StringBuilder

Bonjour, que d'animosité !

Où voyez-vous de l'animosité ? :calim2:
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

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:

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:

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'.

@+

30/08/2015, 17h54
StringBuilder

Citation:

Envoyé par Artemus24

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

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

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 !)

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:

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:

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

31/08/2015, 17h24
StringBuilder

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/
03/09/2015, 20h56
Artemus24
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:

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 ?

@+
03/09/2015, 21h16
Artemus24

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.

@+
03/09/2015, 21h44
SQLpro

1 pièce(s) jointe(s)

Vous voila pris sur le fait par le mensonge....
Au sujet des jointures INNER JOIN, OUTER... , vous affirmez :

Citation:

Envoyé par Artemus24

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 !
Pièce jointe 187109

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...
03/09/2015, 22h03
Artemus24

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é.

@+

Citation:

Envoyé par Artemus24

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:

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

Citation:

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.

Citation:

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.

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:

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:

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:

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:

Code:

id, name, nbVisits

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:

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:

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.

@+

04/09/2015, 19h34
SQLpro

Citation:

Envoyé par Artemus24

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 +
04/09/2015, 21h32
StringBuilder

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.

Citation:

Envoyé par Artemus24

Il y a une petite erreur dans votre requête.
Dans la sous-requête corrélée, vous avez mis :

Code:

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".

Merci d'avoir relevé la coquille, il s'avère que c'était simplement une erreur de copié/collé.

Citation:

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'alléger la requête en lisibilité.

Effectivement le where sur la sous requête est obligatoire, car plusieurs société ayant le même nom peuvent être dans des catégories différentes, et/ou des pays différents, et moi je ne souhaite récupérer le maximum seulement d'un couple (pays, category).

Citation:

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.

Cet index est utiliser par une autre requête. Je vous est lister tous les index que la table possèdes. Mais c'est vrai que cet index n'est pas utiliser pour cet exemple, mais je ne peux le supprimer, car il est utiliser pour une autre requête.

Citation:

Pour l'index, il y a deux points à traiter, soit :

Code:

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:

Code:

id, name, nbVisits

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:

alter table company add index `idx_perf` (`name`,`online`,`valid`,`category_id`,`country`, `id`, `nbVisits`);

Je viens donc d'ajouter cet index "idx_perf" sur la table. Mais cela ne change quasiment rien au perf de la requête.
Citation:
En ce que concerne les tests, je ferrai ceci :
Code:

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.

@+
Sur phpmyadmin quand on coche "profilage" à l'execution d'une requete il fait a peu près ça. J'ai donc ajouter le SQL_NO_CACHE aux requetes, mais cela ne change pas l'ordre de grandeur du résultats. La requête de StringBuilder est toujours à peu près 9 fois plus rapide.

Cordialement.

07/09/2015, 17h56
Artemus24

Salut à tous.

Citation:

Envoyé par jojolebg

Effectivement le where sur la sous requête est obligatoire, car plusieurs société ayant le même nom peuvent être dans des catégories différentes, et/ou des pays différents, et moi je ne souhaite récupérer le maximum seulement d'un couple (pays, category).

Merci pour la précision. Donc on ne peut pas simplifier votre requête.

Citation:

Envoyé par jojolebg

Cet index est utiliser par une autre requête. Je vous est lister tous les index que la table possèdes. Mais c'est vrai que cet index n'est pas utiliser pour cet exemple, mais je ne peux le supprimer, car il est utiliser pour une autre requête.

Je croyais que cet index avait été créé spécialement pour votre requête.

Citation:

Envoyé par jojolebg

Je viens donc d'ajouter cet index "idx_perf" sur la table. Mais cela ne change quasiment rien au perf de la requête.

Désolé mais je ne peux rien faire de plus.

Citation:

Envoyé par jojolebg

Sur phpmyadmin quand on coche "profilage" à l'exécution d'une requête il fait a peu près ça.

Je n'utilise pratiquement pas phpMyAdmin. Je fais tous mes tests en lignes de commandes.

Pour l'astuce que je vous ai donné, on a ainsi un ordre de grandeur de la performance de la requête.
Sinon, il y a une autre façon de tester une requête. Sous MySql, il existe un utilitaire qui permet de lancer un nombre de fois votre requête et d'obtenir la moyenne du temps d'exécution.
Cet utilitaire se nomme 'mysqlslap'. Sur mon mini jeu d'essai, j'ai testé la requête et j'obtiens comme vous, un meilleur résultat pour la solution de StringBuilder.

Citation:

Envoyé par jojolebg

J'ai donc ajouter le SQL_NO_CACHE aux requêtes, mais cela ne change pas l'ordre de grandeur du résultats.

C'est juste pour dire à MySql de ne pas utiliser les buffers pour stocker le résultat de la requête.

Citation:

Envoyé par jojolebg

La requête de StringBuilder est toujours à peu près 9 fois plus rapide.

Impressionnant comme résultat.

J'ai fait une analyse de la comparaison entre la solution de StringBuilder et ma solution.
En vérifiant le résultat de la variable 'handler_read_next' pour chacune des solutions, je constate que si N est le nombre de lignes dans la table, on obtiens en tant que lecture :
--> 3 * N lectures pour la solution de StringBuilder.
--> 5 * N lectures pour ma solution.
C'est assez déconcertant d'obtenir ce genre de résultat. Quel est l'utilité de faire 3 ou 5 passages sur la table pour produire le résultat attendu ?
Je trouve que c'est du grand n'importe quoi l'optimisation de MySql.

Inversement, dans nos deux requêtes, nous faisons un tri. Le nombre de lignes traiter pour le tri est de :
--> N lignes pour la solution de StringBuilder, c'est-à-dire la totalité de la table.
--> un nombre beaucoup plus petit pour ma solution. En fait, le nombre que j'obtiens est celui du nombre de lignes à l'affichage des résultats.

La différence des résultats entre les deux requêtes se fait essentiellement sur 'handler_read_next', c'est-à-dire le nombre de lectures.
J'espère que votre problème est résolu.

Citation:

Envoyé par StringBuilder

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"

Je suis entièrement de votre avis.

Je retiendrais qu'une sous-requête corrélée sous MySql est d'une performance très dégradée, à l'inverse des SGBD de haut de gamme.
Je le savais déjà en ce qui concerne une sous-requête non corrélée, mais je ne m'attendais pas à ce résultat aussi décevant.

En tout cas, la discussion a été enrichissante car on ne peut rien prévoir à l'avance sur les performance d'une requête.

Sinon StringBuilder, j'aimerai savoir comment vous testez la performance d'une requête ?
Désolé si j'ai été un peu trop brusque. Je vous présente mes excuses !

@+

Salut jojolebg.

J'ai bien reçu vos messages et j'ai installé la table 'company_md5'. Il y a 76406 lignes dans votre tables et ce n'est pas énorme.
Je constate que votre table contient ni 'primary key', ni index. Est-ce un oubli ?

Du coup, j'ai fait une comparaison entre ma requête et celle de 'StringBuilder' sans les index. Et voici le résultat de ce test :

Code:

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
--------------
describe company_md5
--------------
 
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   |     | NULL    |       |
| category_id | int(11)      | YES  |     | NULL    |       |
| parent_id   | int(11)      | YES  |     | NULL    |       |
| name        | varchar(255) | NO   |     | NULL    |       |
| isClient    | tinyint(1)   | NO   |     | NULL    |       |
| country     | varchar(80)  | NO   |     | NULL    |       |
| online      | tinyint(1)   | NO   |     | NULL    |       |
| nbVisits    | int(11)      | NO   |     | NULL    |       |
| valid       | tinyint(1)   | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
--------------
ANALYZE TABLE company_md5
--------------
 
+-------------------------+---------+----------+----------+
| Table                   | Op      | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| goafrica_sf.company_md5 | analyze | status   | OK       |
+-------------------------+---------+----------+----------+
--------------
SHOW TABLE STATUS LIKE '%company_md5%'
--------------
 
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| company_md5 | InnoDB |      10 | Compact    | 76164 |             89 |     6832128 |               0 |            0 |  12582912 |           NULL | 2015-09-10 18:59:25 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
--------------
show index from company_md5
--------------
 
--------------
explain SELECT SQL_NO_CACHE   id,   name,   nbVisits FROM company_md5 c WHERE nbVisits = (SELECT MAX(ic.nbVisits) FROM company_md5 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
--------------
 
+----+--------------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+--------------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | PRIMARY            | c     | ALL  | NULL          | NULL | NULL    | NULL | 76164 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | ic    | ALL  | NULL          | NULL | NULL    | NULL | 76164 | Using where                 |
+----+--------------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
--------------
explain SELECT SQL_NO_CACHE c.id, c.name, c.nbVisits FROM company_md5 c                                LEFT OUTER JOIN company_md5 ic    ON ic.name = c.name and ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG'  AND ic.nbVisits > c.nbVisits WHERE c.category_id IN (175)
 AND c.online = 1 AND c.valid = 1 AND c.country = 'TG' AND ic.id IS NULL ORDER BY c.nbVisits DESC limit 5
--------------
 
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                                                          |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------------------+
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL | 76164 | Using where; Using temporary; Using filesort                   |
|  1 | SIMPLE      | ic    | ALL  | NULL          | NULL | NULL    | NULL | 76164 | Using where; Not exists; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------------------------+
--------------
RESET QUERY CACHE
--------------
 
--------------
set profiling=1
--------------
 
--------------
SELECT SQL_NO_CACHE   id,   name,   nbVisits FROM company_md5 c WHERE nbVisits = (SELECT MAX(ic.nbVisits) FROM company_md5 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   o
nline = 1 AND   valid = 1 AND   country = 'TG'                   ORDER BY   nbVisits DESC LIMIT 5
--------------
 
+------+----------------------------------+----------+
| id   | name                             | nbVisits |
+------+----------------------------------+----------+
| 7810 | 1fb55954013c8267149ea1ddd6c9fab7 |    11876 |
|   71 | c0ef41c26ea24a4eed4fded037057d78 |    11086 |
| 1429 | fec2d8acd42fcbdfe05c699878e43886 |     7549 |
|   29 | 4dbe09cc1623e6d2ff7706c3e397cd23 |     6671 |
|   28 | 5c7f48ee250da615e03cf102e39676c7 |     6605 |
+------+----------------------------------+----------+
--------------
SELECT SQL_NO_CACHE c.id, c.name, c.nbVisits FROM company_md5 c                                LEFT OUTER JOIN company_md5 ic    ON ic.name = c.name and ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG'  AND ic.nbVisits > c.nbVisits WHERE c.category_id IN (175) AND c.o
nline = 1 AND c.valid = 1 AND c.country = 'TG' AND ic.id IS NULL ORDER BY c.nbVisits DESC limit 5
--------------
 
+------+----------------------------------+----------+
| id   | name                             | nbVisits |
+------+----------------------------------+----------+
| 7810 | 1fb55954013c8267149ea1ddd6c9fab7 |    11876 |
|   71 | c0ef41c26ea24a4eed4fded037057d78 |    11086 |
| 1429 | fec2d8acd42fcbdfe05c699878e43886 |     7549 |
|   29 | 4dbe09cc1623e6d2ff7706c3e397cd23 |     6671 |
|   28 | 5c7f48ee250da615e03cf102e39676c7 |     6605 |
+------+----------------------------------+----------+
--------------
set profiling=0
--------------
 
--------------
SHOW PROFILES
--------------
 
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------+
| Query_ID | Duration    | Query
                            |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------+
|        1 | 45.34945525 | SELECT SQL_NO_CACHE   id,   name,   nbVisits FROM company_md5 c WHERE nbVisits = (SELECT MAX(ic.nbVisits) FROM company_md5 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
ategory_id IN (175) AND   o |
|        2 |  0.96298800 | SELECT SQL_NO_CACHE c.id, c.name, c.nbVisits FROM company_md5 c                                LEFT OUTER JOIN company_md5 ic    ON ic.name = c.name and ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG'  AND ic.nbVisits > c.nbVisits WHERE c.c
ategory_id IN (175) AND c.o |
+----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------+
 
Appuyez sur une touche pour continuer...

La différence d'exécution est énorme :
--> ma requête dure : 45,34945525 secondes
--> la requête de stringbuiler dure : 0,96298800
Oui, mais voilà, il n'y a ni 'primary key' ni index.

Je crée une 'primary key' sur la colonne 'id' et un index sur les colonnes 'name','online','valid','category_id','country', 'id', 'nbVisits'.
Et je refais un nouveau test.

Code:

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
141
142
143
144
145
146
147
148
149
--------------
alter table company_md5 drop primary key
--------------
 
ERROR 1091 (42000) at line 3: Ne peut effacer (DROP) 'PRIMARY'. Vérifiez s'il existe
--------------
alter table company_md5 add  primary key (`id`)
--------------
 
--------------
alter table company_md5 drop index `idx`
--------------
 
ERROR 1091 (42000) at line 6: Ne peut effacer (DROP) 'idx'. Vérifiez s'il existe
--------------
alter table company_md5 add  index `idx` (`name`,`online`,`valid`,`category_id`,`country`, `id`, `nbVisits`)
--------------
 
--------------
describe company_md5
--------------
 
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| category_id | int(11)      | YES  |     | NULL    |       |
| parent_id   | int(11)      | YES  |     | NULL    |       |
| name        | varchar(255) | NO   | MUL | NULL    |       |
| isClient    | tinyint(1)   | NO   |     | NULL    |       |
| country     | varchar(80)  | NO   |     | NULL    |       |
| online      | tinyint(1)   | NO   |     | NULL    |       |
| nbVisits    | int(11)      | NO   |     | NULL    |       |
| valid       | tinyint(1)   | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
--------------
ANALYZE TABLE company_md5
--------------
 
+-------------------------+---------+----------+----------+
| Table                   | Op      | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| goafrica_sf.company_md5 | analyze | status   | OK       |
+-------------------------+---------+----------+----------+
--------------
SHOW TABLE STATUS LIKE '%company_md5%'
--------------
 
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| company_md5 | InnoDB |      10 | Compact    | 76218 |             89 |     6832128 |               0 |      4734976 |         0 |           NULL | 2015-09-10 19:15:45 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |
+-------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
--------------
show index from company_md5
--------------
 
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| company_md5 |          0 | PRIMARY  |            1 | id          | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
| company_md5 |          1 | idx      |            1 | name        | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
| company_md5 |          1 | idx      |            2 | online      | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
| company_md5 |          1 | idx      |            3 | valid       | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
| company_md5 |          1 | idx      |            4 | category_id | A         |       76218 |     NULL | NULL   | YES  | BTREE      |         |               |
| company_md5 |          1 | idx      |            5 | country     | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
| company_md5 |          1 | idx      |            6 | id          | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
| company_md5 |          1 | idx      |            7 | nbVisits    | A         |       76218 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
explain SELECT SQL_NO_CACHE   id,   name,   nbVisits FROM company_md5 c WHERE nbVisits = (SELECT MAX(ic.nbVisits) FROM company_md5 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
--------------
 
+----+--------------------+-------+-------+---------------+------+---------+--------------------------------------------+-------+------------------------------------------+
| id | select_type        | table | type  | possible_keys | key  | key_len | ref                                        | rows  | Extra                                    |
+----+--------------------+-------+-------+---------------+------+---------+--------------------------------------------+-------+------------------------------------------+
|  1 | PRIMARY            | c     | index | NULL          | idx  | 1024    | NULL                                       | 76218 | Using where; Using index; Using filesort |
|  2 | DEPENDENT SUBQUERY | ic    | ref   | idx           | idx  | 1016    | goafrica_sf.c.name,const,const,const,const |     1 | Using where; Using index                 |
+----+--------------------+-------+-------+---------------+------+---------+--------------------------------------------+-------+------------------------------------------+
--------------
explain SELECT SQL_NO_CACHE c.id, c.name, c.nbVisits FROM company_md5 c                                LEFT OUTER JOIN company_md5 ic    ON ic.name = c.name and ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG'  AND ic.nbVisits > c.nbVisits WHERE c.category_id IN (175)
 AND c.online = 1 AND c.valid = 1 AND c.country = 'TG' AND ic.id IS NULL ORDER BY c.nbVisits DESC limit 5
--------------
 
+----+-------------+-------+-------+---------------+------+---------+--------------------------------------------+-------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref                                        | rows  | Extra                                    |
+----+-------------+-------+-------+---------------+------+---------+--------------------------------------------+-------+------------------------------------------+
|  1 | SIMPLE      | c     | index | NULL          | idx  | 1024    | NULL                                       | 76218 | Using where; Using index; Using filesort |
|  1 | SIMPLE      | ic    | ref   | idx           | idx  | 1016    | goafrica_sf.c.name,const,const,const,const |     1 | Using where; Not exists; Using index     |
+----+-------------+-------+-------+---------------+------+---------+--------------------------------------------+-------+------------------------------------------+
--------------
RESET QUERY CACHE
--------------
 
--------------
set profiling=1
--------------
 
--------------
SELECT SQL_NO_CACHE   id,   name,   nbVisits FROM company_md5 c WHERE nbVisits = (SELECT MAX(ic.nbVisits) FROM company_md5 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   o
nline = 1 AND   valid = 1 AND   country = 'TG'                   ORDER BY   nbVisits DESC LIMIT 5
--------------
 
+------+----------------------------------+----------+
| id   | name                             | nbVisits |
+------+----------------------------------+----------+
| 7810 | 1fb55954013c8267149ea1ddd6c9fab7 |    11876 |
|   71 | c0ef41c26ea24a4eed4fded037057d78 |    11086 |
| 1429 | fec2d8acd42fcbdfe05c699878e43886 |     7549 |
|   29 | 4dbe09cc1623e6d2ff7706c3e397cd23 |     6671 |
|   28 | 5c7f48ee250da615e03cf102e39676c7 |     6605 |
+------+----------------------------------+----------+
--------------
SELECT SQL_NO_CACHE c.id, c.name, c.nbVisits FROM company_md5 c                                LEFT OUTER JOIN company_md5 ic    ON ic.name = c.name and ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG'  AND ic.nbVisits > c.nbVisits WHERE c.category_id IN (175) AND c.o
nline = 1 AND c.valid = 1 AND c.country = 'TG' AND ic.id IS NULL ORDER BY c.nbVisits DESC limit 5
--------------
 
+------+----------------------------------+----------+
| id   | name                             | nbVisits |
+------+----------------------------------+----------+
| 7810 | 1fb55954013c8267149ea1ddd6c9fab7 |    11876 |
|   71 | c0ef41c26ea24a4eed4fded037057d78 |    11086 |
| 1429 | fec2d8acd42fcbdfe05c699878e43886 |     7549 |
|   29 | 4dbe09cc1623e6d2ff7706c3e397cd23 |     6671 |
|   28 | 5c7f48ee250da615e03cf102e39676c7 |     6605 |
+------+----------------------------------+----------+
--------------
set profiling=0
--------------
 
--------------
SHOW PROFILES
--------------
 
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------+
| Query_ID | Duration   | Query
                           |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------+
|        1 | 0.09293775 | SELECT SQL_NO_CACHE   id,   name,   nbVisits FROM company_md5 c WHERE nbVisits = (SELECT MAX(ic.nbVisits) FROM company_md5 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                                  ca
tegory_id IN (175) AND   o |
|        2 | 0.05272950 | SELECT SQL_NO_CACHE c.id, c.name, c.nbVisits FROM company_md5 c                                LEFT OUTER JOIN company_md5 ic    ON ic.name = c.name and ic.online = 1 AND ic.valid = 1 AND ic.category_id IN (175) AND ic.country = 'TG'  AND ic.nbVisits > c.nbVisits WHERE c.ca
tegory_id IN (175) AND c.o |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------+
 
Appuyez sur une touche pour continuer...

Les deux requêtes travaillent sur les mêmes colonnes. Donc l'index que j'ai créé aura une bonne influence sur la performance des deux requêtes.
--> ma requête dure : 0.09293775 secondes
--> la requête de stringbuiler dure : 0,05272950
La plus grosse amélioration reste ma requête avec un rapport de 487,95.
La requête de stringbuiler produit la meilleur performance, même si l'amélioration donne un rapport de 18,26.

Le rapport entre les deux performances donne 1,7625 soit presque le double (c'est ma requête qui dure presque deux fois plus longtemps).
Les deux performances sont faibles, moins de 0,10 seconde. Vu la quantité de lignes, et vu le temps de réponses, c'est presque pareil.
Mais si la volumétrie augmente grandement, je conseille de choisir la requête de 'StringBuiler'.

@+

11/09/2015, 09h58
jojolebg

Citation:

Envoyé par Artemus24

Salut jojolebg.
J'ai bien reçu vos messages et j'ai installé la table 'company_md5'. Il y a 76406 lignes dans votre tables et ce n'est pas énorme.
Je constate que votre table contient ni 'primary key', ni index. Est-ce un oubli ?

C'est pas un oublie, c'était fait exprès, pour vous laisser le choix des index à créer.

Citation:

Les deux requêtes travaillent sur les mêmes colonnes. Donc l'index que j'ai créé aura une bonne influence sur la performance des deux requêtes.
--> ma requête dure : 0.09293775 secondes
--> la requête de stringbuiler dure : 0,05272950
La plus grosse amélioration reste ma requête avec un rapport de 487,95.
La requête de stringbuiler produit la meilleur performance, même si l'amélioration donne un rapport de 18,26.

Le rapport entre les deux performances donne 1,7625 soit presque le double (c'est ma requête qui dure presque deux fois plus longtemps).
Les deux performances sont faibles, moins de 0,10 seconde. Vu la quantité de lignes, et vu le temps de réponses, c'est presque pareil.
Mais si la volumétrie augmente grandement, je conseille de choisir la requête de 'StringBuiler'.

@+

Sur ma machine et mon vrai jeu de données, j'ai un rapport de 8 à peu près. Mais cela reste, comme dit, une petite performance dans les deux cas.

Merci en tout cas d'avoir passé du temps à m'aider.
11/09/2015, 20h40
Artemus24

Salut jojolebg.

Pouvez-vous communiquer le descriptif de votre table company, telle quelle est déclarée en production ?

Et de même, pouvez-vous me communiquer aussi le fichier 'my.ini" que vous utilisez ?

Merci.

@+

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

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