Discussion :

[Gingirou]

Bonsoir,

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

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 :

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

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.

[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 +

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

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 !

[Gingirou]

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 :

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

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 :

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

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 ?

[CinePhil]

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.

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

Code :

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

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.

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

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 :

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

CREATE INDEX #nom_index ON #nom_schem.#nom_table

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

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 :

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

GROUP BY CASTING.idartiste, CASTING.type

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

[Gingirou]

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

Code :

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

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 :

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

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 :

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

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