Requête = ALL

Invité · 17/08/2015, 12h25

Bonjour à tous,

Tout d'abord la structure de la base et des données de tests

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
 
DROP TABLE IF EXISTS Tst21;
DROP TABLE IF EXISTS Tst22;
DROP TABLE IF EXISTS Tst20;
 
CREATE TABLE `Tst20` (
	`IDTst20` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Libellé20` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`IDTst20`),
	INDEX `FK_Tst11_Tst1` (`Libellé20`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
 
CREATE TABLE `Tst22` (
	`IDTst22` INT(10) UNSIGNED NOT NULL,
	`Libellé22` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`IDTst22`),
	INDEX `FK_Tst11_Tst1` (`Libellé22`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
 
CREATE TABLE `Tst21` (
	`IDTst21` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`IDTst22` INT(10) UNSIGNED NOT NULL,
	`IDTst20` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`IDTst21`),
	INDEX `FK_Tst21_Tst20` (`IDTst20`),
	INDEX `FK_Tst21_Tst22` (`IDTst22`),
	CONSTRAINT `FK_Tst21_Tst22` FOREIGN KEY (`IDTst22`) REFERENCES `Tst22` (`IDTst22`),
	CONSTRAINT `FK_Tst21_Tst20` FOREIGN KEY (`IDTst20`) REFERENCES `Tst20` (`IDTst20`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
 
INSERT INTO Tst22(IDTst22,Libellé22) VALUES (1,'L1'),(2,'L2');
INSERT INTO Tst20(IDTst20,Libellé20) VALUES (1,'Libellé 1'),(2,'Libellé 2'),(3,'Libellé 3');
INSERT INTO Tst21(IDTst22,IDTst20) VALUES (1,1),(1,2),(2,2),(1,3),(2,3);

Je voudrais obtenir les enregistrements de Tst20 pour lesquels tous les enregistrements de Tst22 existent dans Tst21.
Dans mon exemple Tst22 = (1,2)
Les enregistrements de Tst20 qui ont tous ces enregistrements (1,2) sont 2 et 3.
Mes test avec "=ALL" ne me retournent pas d'enregitrement

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT
T21.*
FROM
Tst20 T20
INNER JOIN Tst21 T21 ON T21.IDTst20=T20.IDTst20
WHERE
T21.IDTst22 = ALL
(SELECT
IDTst22
FROM
Tst22
)
;

Je sais que je m'y prends mal mais je n'arrive pas à reformuler !

Une idée ?

**SQLpro** · 17/08/2015, 20h14

Il faut faire une division relationnelle. Lisez ce que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/divrelationnelle/

A +

Invité · 17/08/2015, 20h46

Merci mais c'est impossible compte tenu du nombre d'enregistrements (plusieurs millions).

**SQLpro** · 18/08/2015, 15h58

Envoyé par nenex73

Merci mais c'est impossible compte tenu du nombre d'enregistrements (plusieurs millions).

et alors ???? Si c'est ça qui vous gène, alors ne faite aucune requête !!!!

C'est vraiment du grand n'importe quoi....

A +

Invité · 18/08/2015, 16h26

Envoyé par SQLpro

et alors ???? Si c'est ça qui vous gène, alors ne faite aucune requête !!!!

C'est vraiment du grand n'importe quoi....

A +

Cher ami, tout expert MS SQL que vous êtes, votre solution ne fonctionne pas avec MySQL quand les tables manipulées dépassent les 200 millions de tuples pour l'une et le 20 millions pour l'autre.
Alors "ne fonctionne pas" n'est pas tout à fait vrai puisqu'on devrait finir par obtenir un résultat après quelques heures.
Faire de la promo pour vos "petits papiers" c'est sûrement bon pour votre référencement mais totalement inexploitable dans ce contexte.
Mais faites le test et revenez dispenser votre "oh combien précieux" retour d'expérience que nous boirons jusqu'à la lie.

**skuatamad** · 18/08/2015, 17h36

Il n'en reste pas moins que le besoin exprimé est celui de la division relationnelle

Dans le cadre de l'exemple proposée Tst21 est la table d'association entre Tst22 et Tst20.
La clé primaire `IDTst21` telle que créée est inutile, et il serait préférable de créer la clé primaire sur le couple (IDTst22, IDTst20).

Si dans l'état du développement hors simplification pour le forum, la modification de la clé primaire est impossible, il devrait cependant y avoir une clé secondaire fonctionnelle. J'imagine qu'il n'y a pas de sens à ce que le couple (IDTst22, IDTst20) soit doublonné.
Cette clé fonctionnelle peut cependant nécessité la présence d'autres colonnes en fonction de votre besoin.

Donc avec une contrainte d'unicité (en sous jacent, il y aura un index unique) sur le couple (IDTst22, IDTst20), je pense qu'une requête de type division relationnelle codée ansi devrait pouvoir s'en sortir même sur mysql :

Code :

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

1
2
3
4
5
select IDTst20
  from Tst21
 where IDTst22 in (1, 2)
 group by IDTst20
having count(*) = 2  --2 est le nombre d'ocurrence dans le IN

Si la clé fonctionnelle n'est pas uniquement sur (IDTst22, IDTst20), il faudra modifer la clause HAVING :
having count(distinct IDTst22) = 2

Invité · 18/08/2015, 18h42

J'ai réécrit la requête telle que vous m'y invitiez mais elle s'exécute déjà depuis plusieurs minutes et je ne pense pas quelle donnera des résultats avant encore un moment.

On est d'accord, sur le papier ça fonctionne.
C'est même élégant comme solution.
Mais dans la vraie vie, ca rame sec !

Le truc c'est que la version simplifiée de la structure peut (va) induire en erreur.
J'ai 3 tables dans le FROM car dans le having count(*) = x, x dépend de l'une de ces tables.
Et Tst20 possède environ 20 millions d'enreg et Tst21, 200 millions.
Ce n'est pas démentiel mais pour MySQL c'est trop pour ce genre d'opération.

J'ai essayé avec des GROUP_CONCAT comme cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT
T20.IDTst20,
GROUP_CONCAT(T21.IDTst22 ORDER BY T21.IDTst22) AS Signature
FROM
Tst20 T20
INNER JOIN Tst21 T21 ON T21.IDTst20=T20.IDTst20
GROUP BY
T20.IDTst20
HAVING
Signature=(SELECT GROUP_CONCAT(IDTst22 ORDER BY IDTst22) FROM Tst22)

Ca fait le job mais est-ce plus rapide ?

**skuatamad** · 19/08/2015, 11h32

Je ne vois pas l'intérêt du group concat et signature n'était pas décrit dans le jeu de test.

Avant de faire la jointure il faudrait voir les perfs en travaillant uniquement sur Tst21 pour ensuite utiliser cette requête en sous-requête pour récupérer les libellés.

IDTst22 et IDTst20 sont ils bien des entiers ?

Comme mentionné, il vous faudrait à minima un index multi colonne sur le couple (IDTst22, IDTst20) et non un index par colonne.

Dans l'exemple proposé, pour il devrait y avoir selon moi une contrainte d'unicité (qui se matérialise souvent par la création d'un index unique)

Invité · 19/08/2015, 11h59

Signature permet d'obtenir l'ensemble des IDTst22 de Tst22 qui sera comparer à l'ensemble des Tst22 de Tst21 regrouper par IDTst20.
Je filtre ainsi tous les enr de Tst20 qui ne contiennent pas tous les enregistrement de référence (ceux présents dans Tst22).
Bien qu'en réalité, il y ait plus de tables en jeu et qq filtres suplémentaires

Signature n'est qu'un alias du GROUP_CONCAT et n'est pas un champ !

IDTst22 et IDTst20 sont des INT(10) UNSIGNED AUTO_INCREMENT

(IDTst22, IDTst20) fait bien l'objet d'une clé unique.
Mais je ne vois pas à quoi elle sert dans cet exemple car nous n'avons pas de where ni de condition de jointure utilisant cet index multi colonnes ?

**skuatamad** · 19/08/2015, 13h02

Envoyé par nenex73

Signature n'est qu'un alias du GROUP_CONCAT et n'est pas un champ !

Arf je ne l'avais pas vu

Envoyé par nenex73

(IDTst22, IDTst20) fait bien l'objet d'une clé unique.
Mais je ne vois pas à quoi elle sert dans cet exemple car nous n'avons pas de where ni de condition de jointure utilisant cet index multi colonnes ?

Dans celle là si :

Code :

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

1
2
3
4
5
select IDTst20
  from Tst21
 where IDTst22 in (1, 2)
 group by IDTst20
having count(*) = 2  --2 est le nombre d’occurrence dans le IN

Qui, il me semble, est à l'origine de la demande.
Un WHERE + une agrégation, l'index suffit pour résoudre cette requête, mysql n'a pas besoin de lire la table.

Je pense qu'il est préférable de mettre IDTst22 en 1er pour le filtre.

Que donne seulement cette requête (sans jointure pour récupérer les libellés) ?
La machine est elle quand même correctement dimensionnée, niveau RAM et CPU ?

**skuatamad** · 19/08/2015, 13h11

En fait vous voulez dire que

Les enregistrements de Tst20 qui ont tous ces enregistrements (1,2) sont 2 et 3.

C'est parce que 1 et 2 sont les seuls lignes de Tst22, mais que vous voulez travailler sur l'intégeralité de la table Tst22, comme ceci ?

Code :

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

1
2
3
4
select IDTst20
  from Tst21
 group by IDTst20
having count(*) = (select count(*) from Tst22)

Du coup c'est clair que c'est lent, mais bon c'est pas un utilisateur qui va lancer cette requête, donc c'est peut être pas si grave.
Il suffit de lancer en batch et d'écrire dans une table ou un fichier le résultat.

Invité · 19/08/2015, 13h45

Vous avez compris la substantifique moelle du problème !
D'autant qu'il manque qq tables et donc qq jointures pour être conforme à la structure en production.
Sans oublier qq where également.

Du coup c'est clair que c'est lent, mais bon c'est pas un utilisateur qui va lancer cette requête, donc c'est peut être pas si grave.
Il suffit de lancer en batch et d'écrire dans une table ou un fichier le résultat.

Bien vu ! Ce traitement est effectivement déclenché par un événement au milieu de la nuit.
Mais vous savez qu'il y a multitude de choses faites la nuit (sauvegarde, d'autres batch, ..) et il est de bon ton de minimiser les fenêtres d'exécution afin d'enchainer d'autres traitements (actuels ou à venir).
Et je cherchais donc un moyen de réduire la fenêtre consacrée à ce traitement.

Je vais faire qq tests afin d'évaluer l'intérêt du remplacement de mes GROUP_CONCAT par des COUNT() comme vous le suggérez.

Merci

Requête = ALL

Requêtes MySQL

Discussions similaires

Partager

Partager