Discussion :

[clavier12AZQSWX]

bonjour,

Je fais un traitement de jointure en 2 tables , une commande et facture. Une commande n'a qu'une seule facture finale.
La facture contient plusieurs version mais je n'ai besoin que de la dernière (celle pas en etat 'versionning')
J'ai énormément de données "versionning" dans ma table facture.

je voudrais savoir si faire une jointure à 2 critères permet de réduire les ressources de traitement de la requete même si la condition 1 est suffisante.

exemple simplifiée du schéma et des données:

table COMMANDE

com_id com_montant com_ com_facture
-----------------------------------------------------
c1 10 f5
c2 20 f6
c3 30 f7


table FACTURE

fac_id fac_com_id fac_statut
-----------------------------------------------------
f1 null versionning
f2 null versionning
f3 null versionning
f4 null versionning
f5 c1 officielle
f6 c2 officielle
f7 c3 officielle
f8 null versionning
f9 null versionning
f10 null versionning


En gros, est-ce que cette requête R1 est meilleure :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT * FROM 
commande LEFT JOIN facture ON com_id =fac_com_id

que cette autre requête R2 à deux conditions de jointure gauche :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT * FROM 
commande LEFT JOIN facture ON (com_id =fac_com_id AND fac_statut='officielle')

Comme je l'ai dit, la condition com_id =fac_com_id suffit à faire la jointure, mais comme la table externe FACTURE contient bcp de données, je me dis que la filtrer pour la jointure serait une bonne idée, donc en ajoutant AND fac_statut='officielle'

qu'en pensez-vous ?

[SQLpro]

ça ne donne pas les mêmes résultats !

A +

[clavier12AZQSWX]

ça ne donne pas les mêmes résultats !

A +

oh là, vous me faites douter là.

com_id =fac_com_id est une condition suffisante, et à chaque fois qu'elle est remplie fac_statut est déjà à 'officielle'.
avec ou sans le "AND fac_statut='officielle'" , les résultats sont identiques.
non?

si dans cet exemple :


je rajoute dans la table GENDERS le tuple :
4 "robot"

et que j'utilise la condition de jointure gauche :
ON (employees.GenderID=genders.genders.id AND genders.genders.gender<>'robot')

alors, les résultats (les 3 lignes) seront identiques à l'exemple , non ?

LA seconde condition "AND genders.genders.gender<>'robot'" est inutile, je l'ajoute juste pour limiter le Nb de lignes brassées.
Je me trompe ?

[tbc92]

Quels sont les index ou les clés sur ta table 'facture' ?
Si tu as une clé composée sur (fac_statut, fac_com_id) (dans cet ordre), alors oui, il faut ajouter le filtre sur fac_statut.
Dans tous les autres cas, je pense que ça ne changera à peu près rien.

[clavier12AZQSWX]

Quels sont les index ou les clés sur ta table 'facture' ?
Si tu as une clé composée sur (fac_statut, fac_com_id) (dans cet ordre), alors oui, il faut ajouter le filtre sur fac_statut.
Dans tous les autres cas, je pense que ça ne changera à peu près rien.

que des indexes séparés, rien de composé.

Ok, donc dans la table fille "FACTURES", je peux avoir 99% de données inutiles, ça ne me sert à rien de les évincer en ajoutant la condition secondaire AND fac_statut='officielle' dans mon critère de liaison ON ?
Pourquoi j'ai dans ma tête le souvenir que ça permettait au moteur SQL de brasser moins de données avant de faire la jointure et que ça consommerait moins de ressources... ? Juste de la théorie de cours et jamais factuel en pratique ?

[tbc92]

Je comprends que tu as 2 indexes sur cette table.
Peut-être que Mysql fait exception, mais en principe, le moteur va utiliser un index, et un seul.
Si tu précises " and fac_statut='officielle' ", le moteur peut être tenté de passer par cet index ; hop, il élimine 90% des données, c'est tentant. Mais ensuite, il doit parcourir toutes les lignes pour chercher la seule et unique ligne qui a l'identifiant cherché.
Si tu ne précises pas 'and fac_statut='officielle' ", le moteur va utiliser l'index sur fac_com_id, ça va l'emmener directement à LA ligne cherchée (une au maximum, donc pas besoin de l'aider plus).

Si tu as la possibilité de modifier l'index, une clé composée sur fac_statut, fac_com_id (dans cet ordre) serait utile. La recherche dans l'index sera plus rapide, parce que très vite, le moteur ira dans la partie de l'index consacrée à fac_statut='officielle', et la recherche de la ligne utile se fera dans un espace 10 fois plus petit, et toujours avec une technique de type 'index', et pas de lecture séquentielle en lisant toutes les lignes.

Mais évidemment, ajouter cette clé composée, ça a un coût, ce n'est pas forcément une bonne idée.

En fait, l'index sur fac_statut est à peu près sans intérêt. Ca permet d'isoler rapidement les lignes selon fac_statut, ok, mais j'imagine qu'on a systématiquement besoin de faire aussi un lien avec une autre table sur fac_com_id. Et si le système choisit d'utiliser cet index sur fac_statut, il se prive de l'index sur fac_com_id.

[Artemus24]

Salut à tous.

Quand je ne sais pas répondre à une question, je fais le test et je regarde ce que cela donne.

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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
--------------
START TRANSACTION
--------------
 
--------------
-- ======================
--------------
 
--------------
-- Base de Données `base`
--------------
 
--------------
-- ======================
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
-- ================
--------------
 
--------------
-- Table `commande`
--------------
 
--------------
-- ================
--------------
 
--------------
DROP TABLE IF EXISTS `commande`
--------------
 
--------------
CREATE TABLE `commande`
( `com_id`       integer unsigned NOT NULL auto_increment primary key,
  `com_montant`  decimal(15,2)    NOT NULL,
  `fac_id`       integer unsigned NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
-- =========================
--------------
 
--------------
-- Insertion dans `commande`
--------------
 
--------------
-- =========================
--------------
 
--------------
insert into `commande` (`com_montant`,`fac_id`) values
  (10.0, 5),(20.0, 6),(30.0, 7)
--------------
 
--------------
-- ====================
--------------
 
--------------
-- Vidage de `commande`
--------------
 
--------------
-- ====================
--------------
 
--------------
select * from `commande`
--------------
 
+--------+-------------+--------+
| com_id | com_montant | fac_id |
+--------+-------------+--------+
|      1 |       10.00 |      5 |
|      2 |       20.00 |      6 |
|      3 |       30.00 |      7 |
+--------+-------------+--------+
--------------
-- ==============
--------------
 
--------------
-- Table `facture`
--------------
 
--------------
-- ==============
--------------
 
--------------
CREATE TABLE `facture`
( `fac_id`      integer unsigned                     NOT NULL auto_increment primary key,
  `com_id`      integer unsigned                     NULL,
  `fac_statut`  enum('officielle','versionning') NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
-- ========================
--------------
 
--------------
-- Insertion dans `facture`
--------------
 
--------------
-- ========================
--------------
 
--------------
insert into `facture` (`com_id`,`fac_statut`) values
  (null, 'versionning'),
  (null, 'versionning'),
  (null, 'versionning'),
  (null, 'versionning'),
  (1,    'officielle'),
  (2,    'officielle'),
  (3,    'officielle'),
  (null, 'versionning'),
  (null, 'versionning'),
  (null, 'versionning')
--------------
 
--------------
-- ===================
--------------
 
--------------
-- Vidage de `facture`
--------------
 
--------------
-- ===================
--------------
 
--------------
select * from `facture`
--------------
 
+--------+--------+-------------+
| fac_id | com_id | fac_statut  |
+--------+--------+-------------+
|      1 |   NULL | versionning |
|      2 |   NULL | versionning |
|      3 |   NULL | versionning |
|      4 |   NULL | versionning |
|      5 |      1 | officielle  |
|      6 |      2 | officielle  |
|      7 |      3 | officielle  |
|      8 |   NULL | versionning |
|      9 |   NULL | versionning |
|     10 |   NULL | versionning |
+--------+--------+-------------+
--------------
-- =======
--------------
 
--------------
-- Requête
--------------
 
--------------
-- =======
--------------
 
--------------
SELECT    *
     FROM commande as t1
LEFT JOIN facture  as t2
       ON t2.com_id = t1.com_id
--------------
 
+--------+-------------+--------+--------+--------+------------+
| com_id | com_montant | fac_id | fac_id | com_id | fac_statut |
+--------+-------------+--------+--------+--------+------------+
|      1 |       10.00 |      5 |      5 |      1 | officielle |
|      2 |       20.00 |      6 |      6 |      2 | officielle |
|      3 |       30.00 |      7 |      7 |      3 | officielle |
+--------+-------------+--------+--------+--------+------------+
--------------
-- =======
--------------
 
--------------
-- Requête
--------------
 
--------------
-- =======
--------------
 
--------------
SELECT    *
     FROM commande as t1
LEFT JOIN facture  as t2
       ON t2.com_id     = t1.com_id
      AND t2.fac_statut = 'officielle'
--------------
 
+--------+-------------+--------+--------+--------+------------+
| com_id | com_montant | fac_id | fac_id | com_id | fac_statut |
+--------+-------------+--------+--------+--------+------------+
|      1 |       10.00 |      5 |      5 |      1 | officielle |
|      2 |       20.00 |      6 |      6 |      2 | officielle |
|      3 |       30.00 |      7 |      7 |      3 | officielle |
+--------+-------------+--------+--------+--------+------------+
--------------
-- =======
--------------
 
--------------
-- Requête
--------------
 
--------------
-- =======
--------------
 
--------------
SELECT    *
     FROM commande as t1
LEFT JOIN facture  as t2
       ON t2.fac_id = t1.fac_id
--------------
 
+--------+-------------+--------+--------+--------+------------+
| com_id | com_montant | fac_id | fac_id | com_id | fac_statut |
+--------+-------------+--------+--------+--------+------------+
|      1 |       10.00 |      5 |      5 |      1 | officielle |
|      2 |       20.00 |      6 |      6 |      2 | officielle |
|      3 |       30.00 |      7 |      7 |      3 | officielle |
+--------+-------------+--------+--------+--------+------------+
--------------
-- ===
--------------
 
--------------
-- FIN
--------------
 
--------------
-- ===
--------------
 
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Dans cet exemple, le résultat produit par les deux requêtes est identique. Votre jeu d'essai n'est pas représentatif de ce que vous cherchez à faire ou à démontrer ou alors, il y a un problème dans la structure de vos tables, ou pire, je n'ai pas compris ce que vous essayez de faire.

je voudrais savoir si faire une jointure à 2 critères permet de réduire les ressources de traitement de la requête même si la condition 1 est suffisante.

La bonne question est de savoir ce que vous cherchez à réellement faire, et non poser une question qui peut déboucher sur une réponse différente de ce que vous attendez. Nous ne savons rien du traitement qui aboutit à l'état de vos tables et il est difficile de répondre avec exactitude sur la finalité de la bonne requête. La structure de vos tables va impliquer la performance du résultat attendu.

com_id =fac_com_id est une condition suffisante, et à chaque fois qu'elle est remplie fac_statut est déjà à 'officielle'. avec ou sans le "AND fac_statut='officielle'" , les résultats sont identiques.

Il y a quelque chose que je ne comprends pas dans la relation entre la table "commande" et "facture". Qui est la table fille et qui est la table mère ? Mais vu la structure de vos tables, je pense que la table "commande" est la table mère et la table facture la table fille. Pourquoi ?
Parce que votre jointure se fait sur la colonne "com_id", qui est la clef primaire de la table "commande" et sur la colonne "fac_com_id" de la table "facture", qui est a priori une clef étrangère. Sur ce fait, il est aberrant de construire vos tables de cette façon, puisque une ligne de la table fille ne peut exister que si la ligne de la table mère existe aupréalable.

Selon ce que je comprends, la table "commande" va donner le détail de ce qui va se trouver sur la "facture". Il est plus logique d'avoir une jointure de ce type "commande.fac_id = facture.fac_id". Ca ne change rien au résultat produit dans votre exemple mais comme je ne sais rien de ce que vous faites, il est hasardeux de choisir telle ou telle approche.

Je comprends aussi que vous ne savez faire que des index sur une seule colonne. Pourquoi ne pas faire cela sur plusieurs colonne ? Comme par exemple un index composé sur "fac_id" et sur "fac_statut" ?

Il semble que le statut soit une phase intermédiaire à votre traitement et n'entre pas en compte dans la jointure finale que vous appliquez sur vos deux tables. Vous devez revoir la structure de vos tables afin d'être en adéquation avec la finalité de ce que vous chez à faire.

@+

[escartefigue]

Bonjour,

Pour ce qui concerne les performances, la première chose à faire c'est de remplacer SELECT * par la liste des colonnes UTILES à votre traitement.
J'ai écrit un billet de blog expliquant pourquoi il ne faut quasiment jamais utiliser SELECT *, jetez-y un coup d'oeil.
Puis, faites un EXPLAIN de vos requêtes pour voir ce qu'il en est vraiment dans votre contexte.

Ensuite, il ne devrait pas y avoir à la fois le numéro de commande dans la table facture ET le numéro de facture dans la table commande
S'il y a bijection entre commande et facture, alors ces deux types d'entité au niveau conceptuel devraient être fusionnés, ce faisant, au niveau tabulaire, on ne devrait plus avoir qu'une seule table.
S'il n'y a pas bijection, alors la référence à la clef étrangère ne doit être faite que d'un seul coté.

Donc le modèle de données est à revoir !

Accessoirement, dans la deuxième requête, les parenthèses sont inutiles, autant les virer