Bonsoir

Je suis en train de programmer mon blog de A à Z (avec l'excellent cours de M@teo21 ) et j'ai mis au point une teramega-requête du genre :

SELECT 
    b.*,
    DATE_FORMAT(b.date_post, "Le %d-%m-%y") AS date_post,
    a.pseudo AS auteur, 
    r.id AS idrub, 
    r.titre AS titrerub
FROM billets b  
INNER JOIN auteurs a
ON b.auteur_id = a.id
INNER JOIN rubriques r
ON b.rubrique = r.id
WHERE b.visible=1 
AND b.visible_edito=1
AND b.rubrique=:rub
ORDER BY b.id DESC 
LIMIT :de,:nb

Et j'aimerai récupérer, avec cette même requête, le nombre de commentaires (dans une table séparé) associés au billet, je sais qu'il faudrait faire une autre jointure avec un COUNT(commentaires.*) et un GROUP BY mais je n'ai jamais réussi ...

Si vous pouviez m'aider je vous en serai très reconnaissant

Salut,

Vu que tu n'as pas besoin de PHP pour décrire ton problème et que tu ne parles que de SQL, ce sujet a plus sa place en « Bases de données ». Je l'ai donc déplacé vers ce forum.

Bonne continuation !
GuilOooo

C'est possible mais tu vas devoir faire appel à une sous-requête dans cette requête.
Ta requête va donc s'alourdir a l'exécution et en sera moins lisible.

Peut-être es ce mieux de faire une seconde requête dans ta boucle d'affichage à partir de l'id du billet courant.

En général, on évite les requêtes qui mettent en jeu plus de 3 tables...surtout en Web...ce sera mieux de faire plusieurs appels à des requêtes plus simples.

@netdoor.fr : Ne propose jamais une telle méthode, c'est la pire.

Indenter ses requêtes est aussi important qu'indenter son code, n'oublie pas de le faire. Évite toujours l'étoile dans tes requêtes (enfin, SELECT *. Écris toujours le nom des colonnes dont tu as besoin, même si ça signifie écrire toutes les colonnes.

Pour ton problème, c'est plutôt simple, joint une table contenant l'id de la news et le nombre de commentaires :

SELECT 
    b.*, -- change ceci
    DATE_FORMAT(b.date_post, "Le %d-%m-%y") AS date_post,
    a.pseudo AS auteur, 
    r.id AS idrub, 
    r.titre AS titrerub,
    c.nbrComm
FROM billets b  
INNER JOIN auteurs a
    ON b.auteur_id = a.id
INNER JOIN rubriques r
    ON b.rubrique = r.id
INNER JOIN
    (
        SELECT idNews, COUNT(*) as nbrComm
        FROM Commentaires
        GROUP BY idNews
    ) c
    ON c.idNews = b.idNews
WHERE b.visible=1 
    AND b.visible_edito=1
        AND b.rubrique=:rub
ORDER BY b.id DESC 
LIMIT :de,:nb

À adapter...

@GuilOooo Merci gentil modo

@netdoor.fr J'ai essayé, j'obtiens la fameuse erreur 2014 (trip perso) qui dit que le tampon MySQl n'est pas vide (en gros qu'on fait une requête dans un autre sans avoir fait le closeCursor, et si l'on a pas exploité toute sa requête, ça deviens problèmatique ). J'ai eu a mettre en oeuvre de requêtes imbriqués mais j'utilisait les infos voulues avant de fermer, bref pour cette solution c'est non

@Fayden Merci c'est pile ce que je cherchais Mais pourrais-tu m'expliquer comment marchent les parenthèses dan la requête ?

Le Chat Léon

C'est une sorte de table qui contient l'id de la news et le nombre de commentaires dans ta table. En gros, exécute seulement ce petit bout et dis-toi que tu fais la jointure de cette table avec ton autre.

Arg... je viens de la tester mais elle ne me renvoi qu'un seul billet ...

@Fayden

Je pense pas que ce soit la pire: tout dépend du plan d'exécution de ta requête...

Parfois il est plus performant de faire 2 requêtes ou 10 plutôt que de chercher à tout mettre dans une seule...boucler sur les enregistrements d'un resultset est une technique qui ne vient pas de moi, elle est très largement utilisé en TRANSACT SQL, en PL/SQL, etc...

Le comparatif est vite fait: en mesurant le temps d'exécution total, php compris, avec ma méthode et la méthode standard :0)

@lechatleon

Tu as obtenus une telle erreur car tu as utilisé la même connexion pour effectuer une autre requête alors que tu souhaitais encore exploiter le résultat de la première. C'est donc normal. Il faut utiliser une autre ressource mysql.

Utilise un LEFT OUTER JOIN dans la requête de Fayden, histoire de tenir compte des billets sans commentaires, sinon tu n'auras de résultat que pour les billets ayant au moins 1 commentaire.

@netdoor.fr Ne t'inquiète j'ai résolu mon problème sur la 2014 depuis longtemps

Le faire en PHP va être invariablement plus lent. Cette requête est ultra simple hein, c'est pas parce qu'on fait trois jointures que le serveur va exploser. Le travail que le SGBDR, c'est le même que celui que PHP ferait avec une boucle mais en plus optimisé. J'aimerais donc avoir des sources (et un exemple précis) de ce que tu avances.

Et effectivement, cette requête ne renvoie que les news qui ont des commentaires. Tu peux faire un LEFT OUTER JOIN plutôt qu'un INNER JOIN.

Ce que je dis c'est que dans la requête complète tu met tout le résultat du count en mémoire du SGBDR et ensuite celui-ci fait ses jointures (mais je peux me tromper il faut faire un EXPLAIN pour en être certain), ou sur disque si la mémoire du SGBDR ne suffit plus.

C'est toujours un problème de charger autant en mémoire (imagine si tu faisais le group by sur 1 millions de news, ou 12 millions et que tu le veux comme ici dans une table temporaire).

Quand tu boucles en PHP, la consommation mémoire ne concerne que le résultat temporaire du:
SELECT Count(*) FROM Commentaire WHERE IdNew = [valeur courante de new ou je ne sais quoi dans la boucle].

Mais je reste d'accord avec toi, à cette échelle il n'y a aucun problème, c'est un select sur un blog!

Mais SQL Server par exemple pose des verrous de lignes à la lecture. Avec une telle requête, tu verrouillerais toute la table des commentaires, les updates seraient en "attente"...avec la mienne, non).

Ça dépend de beaucoup de chose, l'impact de performance dépend également sous MySQL du mode d'isolation des transactions et du moteur de stockage (MyISAM, InnoDB).

Je comprend ton point de vue , tu as le miens : cette méthode n'est pas la pire sur certains systèmes, au contraire, elle peut te sauver...

netdoor.fr arrête.

Citation : netdoor.fr

Ce que je dis c'est que dans la requête complète tu met tout le résultat du count en mémoire du SGBDR et ensuite celui-ci fait ses jointures (mais je peux me tromper il faut faire un EXPLAIN pour en être certain), ou sur disque si la mémoire du SGBDR ne suffit plus.

Dans cette requête Fayden ne met rien nulle part.
Il écrit une requête. Il laisse le soin à l'optimiseur du SGBD de faire son travail avec intelligence.

Citation : netdoor.fr

C'est toujours un problème de charger autant en mémoire (imagine si tu faisais le group by sur 1 millions de news, ou 12 millions et que tu le veux comme ici dans une table temporaire).

J'imagine, j'imagine, mais je n'ai pas beaucoup d'imagination, je cherche les faits.

Citation : netdoor.fr

Quand tu boucles en PHP, la consommation mémoire ne concerne que le résultat temporaire du:
SELECT Count(*) FROM Commentaire WHERE IdNew = [valeur courante de new ou je ne sais quoi dans la boucle].

Ça a l'aire très intéressant comme boucle en tout cas.

Citation : netdoor.fr

Mais SQL Server par exemple pose des verrous de lignes à la lecture. Avec une telle requête, tu verrouillerais toute la table des commentaires, les updates seraient en "attente"...avec la mienne, non).

C'est faux, ou volontaire et idiot.

Citation

En général, on évite les requêtes qui mettent en jeu plus de 3 tables...surtout en Web...ce sera mieux de faire plusieurs appels à des requêtes plus simples.

Y en a aussi qui évitent de faire plus de 3 tables, encore moins besoin de réfléchir, les requêtes sont plus simples.

dans la requête suivante je fais une sous-requête avec une fonction d'agrégation.
Le PEL montre bien que c'est l'ensemble du résultat qui va être chargé en mémoire.

mysql> EXPLAIN EXTENDED SELECT * FROM `Membre` A, (SELECT IdMembre, Count(*) FROM Connexion GROUP BY IdMembre) B WHERE A.Id = B.IdMembre;
+----+-------------+------------+--------+---------------+----------+---------+------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+----------+---------+------------+--------+----------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 220 | 100.00 | |
| 1 | PRIMARY | A | eq_ref | PRIMARY,Id | PRIMARY | 4 | B.IdMembre | 1 | 100.00 | |
| 2 | DERIVED | Connexion | index | NULL | IdMembre | 5 | NULL | 861475 | 100.00 | Using index |
+----+-------------+------------+--------+---------------+----------+---------+------------+--------+----------+-------------+
3 rows in set, 1 warning (0.96 sec)

220 rows

C'est ensuite sur cet ensemble non restreint que la jointure est faite...c'est à dire de manière non optimisée, du fait que j'utilise une fonction d'agrégation dans la sous requête...

Le temps de 0.96 secondes (quad core xeon 3ghz et 4Go ram) pour exécuter la requête montre bien un début de problème de performance puisqu'il y a a peine 861475 record dans la table connexion. La jointure se fait sur des index primary, ce qui montre que la jointure se fait bien de manière optimale pour le SGBDR. L'optimizer a fonctionné comme il fallait par rapport a LA requête donnée.


@cintre sournois

la doc SQL Server sur le comportement par défaut des verrous de lignes:

http://msdn.microsoft.com/fr-fr/library/ms173763.aspx

"Le moteur de base de données utilise des verrous partagés pour empêcher d'autres transactions de modifier des lignes pendant que la transaction active exécute une opération de lecture. Les verrous partagés empêchent également l'instruction de lire des lignes modifiées par d'autres transactions, tant que celles-ci ne sont pas terminées."

++

Et alors ?
Et ben, si avec ça tu parts foutre des requêtes avec des boucles dans ton code.... je plain celui qui passera derrière...

Et pour finir, la preuve que en plus c'est plus rapide:

$temps_debut = microtime(true);

$idsMembre = Database::multipleScalarQuery("SELECT DISTINCT A.Id FROM Membre A, Connexion B WHERE A.Id = B.IdMembre");

foreach($idsMembre as $idMembre)
{
	$count = Database::scalarQuery('SELECT Count(IdMembre) FROM Connexion WHERE IdMembre = '.$idMembre);
}

$temps_fin = microtime(true);

$temps_execution = round($temps_fin - $temps_debut, 4);

echo $temps_execution;

0.0405 (première fois)
0.0288 (seconde fois)
etc en 0.02xxx a cause de la mise en cache des requêtes SIMPLES de la boucle / première fois.


!!!!!!!!!

contre 0.96 avec la sous requête d'agrégation imbriquée sur la même machine hors production sur laquelle je suis seul.

Voilà les FAITS

Si on arrive au même résultat sur une page Web en utilisant 40 fois moins de temps, moins de mémoire, moins de verrous et avec seulement 3 lignes de codes de plus, alors OUI, IL FAUT LE FAIRE!

Je vous ai tout prouvé par A + B messieurs

C'est magnifique.

Je n'étais pas convaincu, j'ai fait le test moi-même.

But : Afficher le titre, l'id et le nombre de commentaires de 50 news.

Méthode 1 avec une jointure : 0.004 sec
Méthode 2 avec une boucle : 0.04 sec

Oups...

Hello, je comprend pas...

Les tests ont été faits sous PostgreSQL, j'ai oublié de préciser. Mais bon, la méthode la plus fiable serait directement avec le SGBDR et comme je ne sais pas faire des boucles en SQL...

Oui je comprend pour les boulces, en SQL je sais pas faire non plus , j'ai plus trop d'idées la, mon lit m'appelle

Mais quoiqu'il en soit, pour un blog, ta solution est la meilleure, la plus intuitive et la moins sujette à débat lol

++

Merci pour toutes ces infos x) Mais dans un souci de clarté du code je préfère tout de même utiliser une seule requête SQL.

Merci encore