Optimisation requête avec jointures

Version imprimable

Bonsoir,

J'essaye désespérément d'optimiser une requête avec pas mal de jointures qui me retourne l'erreur #1104 -

Citation:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

.

J'ai bien rajouté la commande SET SQL_BIG_SELECTS=1 mais cela prend encore pas mal de temps à l'exécution. J'ai d'ailleurs noté dans un rapport du manager ovh de mon sql privé un problème qui peut peut être améliorer les performances : Jointures effectuée sans utiliser d'index.

Je vous joins un bout de la requête qui déjà me retourne l'erreur, il s'agit de calculer la note moyenne de chaque artiste en fonction de critiques et de sa présence dans le casting :
Code:

1 2 3 4 5 6 SELECT CAST.idartiste AS idartisteN, AVG( CRIT.note ) AS Moyenne, CAST.type AS typeartiste FROM table_casting CAST, table_critique CRIT WHERE CAST.idfilm = CRIT.idarticle GROUP BY CAST.idartiste ORDER BY Moyenne DESC LIMIT 0 , 2000
si vous avez une idée sur la façon d'optimiser cela, je suis preneur !

lionel.

13/11/2012, 06h31
SQLpro

Commencez par respecter la charte de postage : http://www.developpez.net/forums/a69...gage-sql-lire/

Sans cela il est impossible de vous aider.

Donnez aussi la liste des index et le plan de requête.

A +
13/11/2012, 09h25
CinePhil

1) CAST est un mauvais nom d'alias car c'est un mot réservé du langage SQL.

2) Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

3) Les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !
J'ai de plus de gros doutes sur la condition de jointure !
J'imagine qu'il peut y avoir plusieurs critiques pour un film et je trouve bizarre cette égalité entre l'identifiant du film et celui de l'article ! 8O

4) Si effectivement vos tables ne sont pas indexées, il est grand temps d'y remédier !

Bref, vous avez besoin d'une grosse révision en matière de BDDR et de SQL !

Excusez moi de ne pas avoir respecté la norme de postage, je suis un novice sur le forum.

J'ai réécrit la requête en tenant compte de deux remarques (modification de la variable CAST et la syntaxe JOIN).
Code:

1 2 3 4 5 6 SELECT CASTING.idartiste AS idartisteN, AVG( CRIT.note ) AS Moyenne, CASTING.type AS typeartiste FROM cinefamilia_casting CASTING LEFT JOIN cinefamilia_critique CRIT ON CRIT.idarticle=CASTING.idfilm GROUP BY CASTING.idartiste ORDER BY Moyenne DESC LIMIT 0 , 100
Pour ce qui est du plan de requête, je ne connaissais pas et je viens de voir qu'il fallait rajouter EXPLAIN devant la requête. J'obtiens cela (je ne sais pas comment l'afficher correctement) mais je n'y comprends pas grand chose :

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CASTING ALL NULL NULL NULL NULL 50362 Using temporary; Using filesort
1 SIMPLE CRIT ALL NULL NULL NULL NULL 4525

Concernant le problème sur les index (j'utilise phpmyadmin et Mysql), je ne sais pas si c'est tout à fait cela, mais je déclare des clefs primaires sur chaque table, et je n'ai jamais utilisé ce genre de syntaxe :

Code:

CREATE INDEX #nom_index ON #nom_schem.#nom_table

Je n'ai malheureusement pas compris le problème sur mon GROUP BY. Quelle est la syntaxe qui permet de faire figurer dans le GROUP BY des colonnes qui n'y sont pas sujettes ?

Citation:

Envoyé par Gingirou

Excusez moi de ne pas avoir respecté la norme de postage, je suis un novice sur le forum.

Nous sommes tous passés par là.
Il nous faudrait la composition de tes tables, par exemple à l'aide du résultat complet des requêtes SHOW CREATE TABLE la_table.
Citation:
J'ai réécrit la requête en tenant compte de deux remarques (modification de la variable CAST et la syntaxe JOIN).
Code:

1 2 3 4 5 6 SELECT CASTING.idartiste AS idartisteN, AVG( CRIT.note ) AS Moyenne, CASTING.type AS typeartiste FROM cinefamilia_casting CASTING LEFT JOIN cinefamilia_critique CRIT ON CRIT.idarticle=CASTING.idfilm GROUP BY CASTING.idartiste ORDER BY Moyenne DESC LIMIT 0 , 100
C'est mieux. Autant utiliser des alisa les plus cours possibles ; 'ca' pour casting et 'cr' pour critique aurait été suffisant à la compréhension de la requête et aurait allégé son écriture et sa lecture.

Citation:

Pour ce qui est du plan de requête, je ne connaissais pas et je viens de voir qu'il fallait rajouter EXPLAIN devant la requête. J'obtiens cela (je ne sais pas comment l'afficher correctement) mais je n'y comprends pas grand chose :

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CASTING ALL NULL NULL NULL NULL 50362 Using temporary; Using filesort
1 SIMPLE CRIT ALL NULL NULL NULL NULL 4525

Visiblement, la requête n'utilise aucun index (possible_keys, key et key_len à NULL).

Citation:

Concernant le problème sur les index (j'utilise phpmyadmin et Mysql), je ne sais pas si c'est tout à fait cela, mais je déclare des clefs primaires sur chaque table, et je n'ai jamais utilisé ce genre de syntaxe :

Code:

CREATE INDEX #nom_index ON #nom_schem.#nom_table

Donne la composition complète des tables (voir plus haut).

Citation:

Je n'ai malheureusement pas compris le problème sur mon GROUP BY. Quelle est la syntaxe qui permet de faire figurer dans le GROUP BY des colonnes qui n'y sont pas sujettes ?

Dans le SELECT, tu as 3 colonnes de résultat :
- CASTING.idartiste ;
- CASTING.type ;
- AVG( CRIT.note ).
CRIT.note fait l'objet d'une opération de groupage (AVG) mais pas les deux autres. Il faut donc que les deux autres colonnes figurent dans le GROUP BY :

Code:

GROUP BY CASTING.idartiste, CASTING.type

Mais je suis toujours étonné par la condition de jointure ! 8O

J'ai fait des show create table des 2 tables qui interviennent dans la requête :

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE `cinefamilia_casting` (
 `id` bigint(255) NOT NULL AUTO_INCREMENT,
 `idartiste` bigint(255) NOT NULL,
 `idfilm` bigint(255) NOT NULL,
 `type` varchar(255) NOT NULL,
 `ordre` bigint(255) NOT NULL,
 `typecasting` varchar(255) NOT NULL DEFAULT 'film',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=52182 DEFAULT CHARSET=utf8

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `cinefamilia_critique` (
 `id` bigint(255) NOT NULL AUTO_INCREMENT,
 `iduser` varchar(255) NOT NULL,
 `idarticle` bigint(255) NOT NULL,
 `txt` longtext NOT NULL,
 `note` float(10,1) NOT NULL,
 `actif` varchar(255) NOT NULL,
 `date` datetime NOT NULL,
 `date_modif` datetime NOT NULL,
 `type` varchar(255) NOT NULL DEFAULT 'film',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4628 DEFAULT CHARSET=utf8

J'ai réécrit la requête comme tu me l'as dit, avec des alias plus courts et le GROUP BY sur les type et idartiste (mais cela va donner un autre résultat:

Code:

1
2
3
4
5
SELECT CA.idartiste AS idartisteN, AVG( CR.note ) AS Moyenne, CA.type AS typeartiste
FROM cinefamilia_casting CA LEFT JOIN cinefamilia_critique CR ON CR.idarticle=CA.idfilm
GROUP BY CA.idartiste, CA.type
ORDER BY Moyenne DESC
LIMIT 0 , 100

Quand j'exécute la requête sous phpmyadmin cela me plante la page...

14/11/2012, 13h47
CinePhil

Ça manque d'index !
Il faudrait indexer au moins les colonnes intervenant dans la condition de jointure (que je ne comprends toujours pas, un film n'étant pas un article et un article n'étant pas un film ! 8O ) :
- idarticle ;
- idfilm

Et tant qu'on y est, puisque ce sont vraissemblablement des clés étrangères, même si les contraintes de clés étrangères ne sont pas utilisées par le moteur MyISAM, indexe aussi :
- idartiste ;
- iduser.

D'une manière générale, j'ai l'impression que c'est carrément le modèle de données qui est à revoir. Les colonnes type et typecasting seraient à externaliser. D'ailleurs que contient la colonne "type" ?

Ça m semble très fouillis tout ça ! :aie:

J'avais mis le nom idarticle parce que je voulais que le modèle s'applique à autre chose que des films (livre...), bref ce n'est effectivement pas cohérent.

La table artistetype c'est pour définir les différents postes liés au film.
Code:

1 2 3 4 5 6 CREATE TABLE `cinefamilia_artistetype` ( `id` bigint(255) NOT NULL AUTO_INCREMENT, `nom` varchar(255) NOT NULL, `ordre` bigint(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
Je n'ai jamais indexé autre chose que des clefs primaires, la manipulation qui consiste à créer un index sur une table est bien celle là ?

Code:

ALTER TABLE `cinefamilia_casting` ADD INDEX ( `idfilm` )

J'ai maintenant indexé les quatre champs et j'ai refait le plan de requêtes, j'obtiens ça :
Code:

1 2 3 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CA ALL NULL NULL NULL NULL 50434 Using temporary; Using filesort 1 SIMPLE CR ref idarticle idarticle 8 cinefamilia.CA.idfilm 19
c'est mieux ?

15/11/2012, 13h56
Fred_34

N'est-ce pas plutôt "INNER JOIN" au lieu de "LEFT JOIN" ?

Citation:

Envoyé par Gingirou

J'avais mis le nom idarticle parce que je voulais que le modèle s'applique à autre chose que des films (livre...), bref ce n'est effectivement pas cohérent.

Il n'empêche qu'un article est un article et qu'un film est un film. Quel est, dans votre cas, le sens du mot "article" ?
Y aurait-il un héritage de données de article vers film et livre ?
Votre structure est difficilement lisible. Si vous pouviez fournir un schéma de la BDD, ça aiderait ; mais en avez-vous fait un ?
Citation:
La table artistetype c'est pour définir les différents postes liés au film.
Code:

1 2 3 4 5 6 CREATE TABLE `cinefamilia_artistetype` ( `id` bigint(255) NOT NULL AUTO_INCREMENT, `nom` varchar(255) NOT NULL, `ordre` bigint(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
Et donc dans la table cinefamilia_casting, c'est la colonne type qui référence l'identifiant de cinefamilia_artistetype ?
Si un artiste peut avoir plusieurs types sur un même film, il est normal qu'ajouter cette colonne dans le groupage de votre requête donne un résultat différent mais si on ne la met pas dans le groupage, il ne faut pas la mettre non plus dans le SELECT car elle n'aurait aucune signification, sa valeur étant aléatoire.

Citation:

Je n'ai jamais indexé autre chose que des clefs primaires, la manipulation qui consiste à créer un index sur une table est bien celle là ?

Code:

ALTER TABLE `cinefamilia_casting` ADD INDEX ( `idfilm` )

Oui.
Citation:
J'ai maintenant indexé les quatre champs et j'ai refait le plan de requêtes, j'obtiens ça :
Code:

1 2 3 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CA ALL NULL NULL NULL NULL 50434 Using temporary; Using filesort 1 SIMPLE CR ref idarticle idarticle 8 cinefamilia.CA.idfilm 19
c'est mieux ?
Oui, maintenant l'index est utilisé pour la seconde table.
Comme le suggère Fred_34, une jointure interne semble plus appropriée. Que donne la moyenne pour un artiste qui n'a pas été noté ? :?

Citation:

Il n'empêche qu'un article est un article et qu'un film est un film. Quel est, dans votre cas, le sens du mot "article" ?

J'ai orienté ma construction autour des fiches articles. A la base j'ai des articles sur lesquels je met des critiques avec notation. Ces articles s’interprètent aussi bien comme fiche ciné ou fiche livre, tout dépend de leur contenu. Je leur attribue ensuite un "casting" avec un type correspondant au poste.

Citation:

Y aurait-il un héritage de données de article vers film et livre ?

Je ne crois pas, ou autrement c'est involontaire.

Citation:

Votre structure est difficilement lisible. Si vous pouviez fournir un schéma de la BDD, ça aiderait ; mais en avez-vous fait un ?

J'ai vu que vous conseillez Open ModelSphere, je l'ai chargé et j'ai essayé de faire un schéma, je ne pense pas que le résultat soit concluant. Je dois vous avouer que je ne fais jamais ce travail préparatoire, je suis un autodidacte "bricoleur".

Citation:

Et donc dans la table cinefamilia_casting, c'est la colonne type qui référence l'identifiant de cinefamilia_artistetype ?

Oui

Citation:

Si un artiste peut avoir plusieurs types sur un même film, il est normal qu'ajouter cette colonne dans le groupage de votre requête donne un résultat différent mais si on ne la met pas dans le groupage, il ne faut pas la mettre non plus dans le SELECT car elle n'aurait aucune signification, sa valeur étant aléatoire.

Effectivement, mais il me faut à un moment donné cette information (peut être dans une deuxième requête du coup). En effet, je calcule des top artistes en fonction des types. Pour le top acteur, réalisateur je dois trouver une façon de vérifier que l'artiste sélectionné n'a pas dans son calcul un type monteur ou compositeur.

Citation:

Comme le suggère Fred_34, une jointure interne semble plus appropriée.

J'ai réécrit la requête, il faut que je me documente pour voir la différence entre un left join et un inner join, j'utilise tout le temps un left join sans réfléchir (quand je n'ai pas la version archaïque de la requête) :
Code:

1 2 3 4 5 SELECT CA.idartiste AS idartisteN, AVG( CR.note ) AS Moyenne FROM cinefamilia_casting CA INNER JOIN cinefamilia_critique CR ON CR.idarticle=CA.idfilm GROUP BY CA.idartiste ORDER BY Moyenne DESC LIMIT 0 , 100
Citation:

Que donne la moyenne pour un artiste qui n'a pas été noté ?

En principe ce n'est pas sensé arriver, chaque fiche article est critiquée, elle a donc une note dont l'artiste hérite si il a été associé à la fiche via le casting.