Discussion :

[Bisûnûrs]

D'ailleurs, concernant la vue que je vais devoir faire, elle va devoir contenir 300 jointures et le résultat aura 300 colonnes, vu que j'ai environ 300 critères ... et le tout sur 20M de membres.

:/

[CinePhil]

De quelle vue parles-tu ?
Attention à ne pas faire de la présentation de données (cosmétique) dans le SGBD !

[Richard_35]

Bonjour à tous,

Désolé d'avoir laissé ce fil (résolu) en plan.

CinePhil et moi sommes sur deux plans sensiblement différents. D'où, l'insistance...

1°/

Un critère libre est un critère dont le membre peut remplir lui-même la valeur

==> OK : existe-t-il des cas de "critères libres" dont la cohérence des couples {thème, critère} doit être contrôlée ?

2°/

Un critère restreint est un critère dont le membre va choisir une valeur parmi une liste de valeurs autorisées pour ce critère.

==> si j'ai bien compris, c'est le cas de :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Application_1  Toto
Application_1  Tata
Application_1  Titi
Application_1  Tutu
Application_2  Twtw
Application_2  Txtx
Application_2  Tyty
Application_2  Tztz
Application_2  Twtw
Application_2  Tata
Application_1  Tztz

qui sera rempli "sauvagement" par l'administrateur.

[Bisûnûrs]

De quelle vue parles-tu ?
Attention à ne pas faire de la présentation de données (cosmétique) dans le SGBD !

Effectivement ... N'étant pas vraiment familiarisé avec les vues, tu me conseilles quoi ?

==> OK : existe-t-il des cas de "critères libres" dont la cohérence des couples {thème, critère} doit être contrôlée ?

Pas à ma connaissance, mais on peut/doit évidemment envisager le cas, par exemple en mettant une autre table de valeurs autorisées entre membre, thème et critère, comme l'a déjà souligné CinePhil.

==> si j'ai bien compris, c'est le cas de :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Application_1  Toto
Application_1  Tata
Application_1  Titi
Application_1  Tutu
Application_2  Twtw
Application_2  Txtx
Application_2  Tyty
Application_2  Tztz
Application_2  Twtw
Application_2  Tata
Application_1  Tztz

qui sera rempli "sauvagement" par l'administrateur.

Non, c'est le cas de la couleur de cheveux. Le pseudo est libre ...

[CinePhil]

Ce que je sous-entendais en évoquant une vue, c'est que l'ensemble des critères des membres peut être récupéré par une vue, même si les informations sont stockées dans des tables différentes.
Il suffit alors de faire une requête sur la vue pour récupérer les critères renseignés par un membre en ajoutant la clause WHERE sur le membre choisi.
Idem pour les critères d'un thème...

[Bisûnûrs]

Ce que je sous-entendais en évoquant une vue, c'est que l'ensemble des critères des membres peut être récupéré par une vue, même si les informations sont stockées dans des tables différentes.

J'avais bien compris le concept
Seulement je ne vois pas comment créer ce type de vues sans passer par la présentation de données.

[CinePhil]

Je pensais à une UNION des différentes associations.

Je reprends mon schéma global :

|-------0,n----décrire----------------------------------------------|
membre -0,n----décrire----0,n- critere -0,n----avoir----(1,1)- valeur_autorisee
|                                  |
|-------0,n----valoriser----0,n----|
                   |
theme -0,n---------|

Commençons par l'association la plus simple :
membre -0,n----décrire----0,n- critere

Tables :
membre (mbr_id...)
critere (crt_id, crt_libelle...)
mbr_decrire_crt (mdc_id_membre, mdc_id_critere, mdc_valeur)

La requête ci-dessous va donner tous les critères définis librement par les membres :

Code SQL :

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

1
2
3
4
SELECT mdc_id_critere AS id_critere
	mdc_valeur AS valeur,
	mdc_id_membre AS id_membre
FROM mbr_decrire_crt

Prenons ensuite l'association ternaire :

membre -0,n----valoriser----0,n- critere
                   |
theme -0,n---------|

Tables :
theme (thm_id, thm_libelle...)
mbr_valoriser_crt_dans_thm_mvct (mvct_id_membre, mvct_idcritere, mvct_id_theme, mvct_valeur)

La requête ci-dessous donnera les critères valorisés librement dans les thèmes par les membres :

Code SQL :

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

1
2
3
4
5
SELECT mvct_id_critere AS id_critere,
	mvct_valeur AS valeur,
	mvct_id_membre AS id_membre,
	mvct_id_theme AS id_theme
FROM mbr_valoriser_crt_dans_thm_mvct

Pour faire une union des deux requêtes, il faut ajouter la colonne id_theme à la première :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT mdc_id_critere AS id_critere
	mdc_valeur AS valeur,
	mdc_id_membre AS id_membre
	NULL AS id_theme
FROM mbr_decrire_crt_mdc
UNION
SELECT mvct_id_critere AS id_critere,
	mvct_valeur AS valeur,
	mvct_id_membre AS id_membre,
	mvct_id_theme AS id_theme
FROM mbr_valoriser_crt_dans_thm_mvct

J'ai ainsi tous les critères définis librement par les membres, hors thème et dans les thèmes.

Je peux transformer cette requête en vue :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE VIEW v_criteres_valorises AS
SELECT mdc_id_critere AS id_critere
	mdc_valeur AS valeur,
	mdc_id_membre AS id_membre
	NULL AS id_theme
FROM mbr_decrire_crt_mdc
UNION
SELECT mvct_id_critere AS id_critere,
	mvct_valeur AS valeur,
	mvct_id_membre AS id_membre,
	mvct_id_theme AS id_theme
FROM mbr_valoriser_crt_dans_thm_mvct

Puis je peux faire une requête sur cette vue en l'associant à la table des thèmes et à celle des critères pour avoir les libellés si nécessaire et en restreignant à l'id_membre = 153 par exemple :

Code SQL :

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

1
2
3
4
5
6
7
SELECT c.crt_id, c.crt_libelle,
	t.thm_id, t.thm_libelle,
	v.valeur
FROM v_criteres_valorises v
LEFT OUTER JOIN critere c ON c.crt_id = v.id_critere
LEFT OUTER JOIN theme t ON t.thm_id = v.id_theme
WHERE v.id_membre = 153

Je t'ai donné la méthode, tu peux compléter la vue avec l'UNION des autres associations du schéma pour récupérer tous les critères valorisés.

Bon courage !

[Bisûnûrs]

Merci CinePhil !

En fait, j'avais déjà essayé aussi la vue avec un UNION, sauf que la requête que j'ai tenté de faire dessus n'a jamais voulu revenir, j'ai dû la tuer.

Pour info, un COUNT sur ma table membre_theme_critere me donne 8M de lignes environ, et sur ma table membre_critere le COUNT me sort environ 73M de lignes.

Après avoir créé la vue un simple :

Code sql :

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

1
2
3
4
5
6
SELECT
  *
FROM
  bench_v
WHERE
  v.id_membre = 81;

met une plombe. Je n'ai pas attendu la fin de la requête. Un SHOW FULL PROCESSLIST; m'a montré que la requête est passée par l'état Converting HEAP to MyISAM (mes tables sont en InnoDB).

Alors que la requête suivante s'exécute instantanément :

Code sql :

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
(
  SELECT
    mtc.id_membre,
    mtc.id_critere,
    mtc.id_theme,
    mtc.valeur
  FROM
    bench.membre_theme_critere mtc
  WHERE
    mtc.id_membre = 81
)
UNION
(
  SELECT
    mc.id_membre,
    mc.id_critere,
    0,
    mc.valeur
  FROM
    bench.membre_critere mc
  WHERE
    mc.id_membre = 81
);

J'imagine qu'avec la vue le moteur ne peut pas utiliser les index et de ce fait les requêtes ne sont pas performantes.

[CinePhil]

J'imagine qu'avec la vue le moteur ne peut pas utiliser les index et de ce fait les requêtes ne sont pas performantes.

Alors ça ce serait un gros bug !
Un de plus avec le mauvais MySQL ?

Tes tables sont correctement indexées au moins ?
Et tous les identifiants sont bien des entiers ?

[Bisûnûrs]

Alors ça ce serait un gros bug !

La doc :
http://dev.mysql.com/doc/refman/5.5/...trictions.html
nous dit :

Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is not permitted.

Tes tables sont correctement indexées au moins ?
Et tous les identifiants sont bien des entiers ?

Oui et oui.

[CinePhil]

Décidément, MySQL est vraiment poudre aux yeux !

[Bisûnûrs]

Décidément, MySQL est vraiment poudre aux yeux !

J'avais déjà lu ce billet de SQLpro oui
Va falloir que je teste ça avec PostgreSQL pour voir ce que ça donne.

En restant sur MySQL et avec la requête UNION, un autre cas me pose problème c'est faire une recherche sur des membres. Autant c'est rapide pour trouver les données d'un membre en particulier, mais si on veut rechercher des membres sur plusieurs valeurs de critères, il faut conjuguer les conditions OR et ça devient rapidement lourd aussi ...