[Resolu] [phpBB] Ajouter colonne "Nb de sujets" dans memberlist.php

Marsh Posté le 13-03-2008 à 17:53:48

Bonjour,

J'ai rajouté une colonne "Sujets" dans memberlist.php qui affiche le nombre de sujets de chaque membre.
J'utilise une Jointure avec "INNER JOIN" et "t.topic_poster = u.user_id"
Or tous les membres qui n'ont créé aucun topic sont ignoré en faisant comme ça.

Il faudrait utiliser "LEFT JOIN" pour que tout s'affiche.

Mais voilà mon problème, dès que je remplace "INNER JOIN" par "LEFT JOIN", la requête devient très longue. Elle passe d'environ ~~0,15~~ 0.0740 seconde d'exécution à plus de 40 secondes.

Voici le code php de la requête avec INNER JOIN:

Code :

$sql = "SELECT u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster, COUNT(t.topic_id) AS count_topic_nb
    FROM " . USERS_TABLE . " u INNER JOIN " . TOPICS_TABLE . " t ON t.topic_poster = u.user_id
    WHERE u.user_id <> " . ANONYMOUS . "$letter_sql
    GROUP BY u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster
    $order_by";
//-- mod : flags ---------------------------------------------------------------
//-- add
    $sql = str_replace('SELECT ', 'SELECT u.user_flag, ', $sql);
    $sql = str_replace('GROUP BY ', 'GROUP BY u.user_flag, ', $sql);
//-- fin mod : flags -----------------------------------------------------------    
//-- mod : rank color system ---------------------------------------------------
//-- add
$sql = str_replace('SELECT ', 'SELECT u.user_level, u.user_color, u.user_group_id, ', $sql);
$sql = str_replace('GROUP BY ', 'GROUP BY u.user_level, u.user_color, u.user_group_id, ', $sql);
//-- fin mod : rank color system -----------------------------------------------

Ce qui donne ceci :

Code :

SELECT u.user_level, u.user_color, u.user_group_id, u.user_flag, u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster, COUNT(t.topic_id) AS count_topic_nb 
FROM table_users u 
  INNER JOIN table_topics t 
    ON t.topic_poster = u.user_id 
WHERE u.user_id <> -1 
  AND u.username LIKE '%' 
GROUP BY u.user_level, u.user_color, u.user_group_id, u.user_flag, u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster 
ORDER BY u.user_regdate ASC 
LIMIT 0, 30

Sauriez-vous me dire ce qui ne va pas ?

Merci d'avance pour votre aide.

Message édité par mkex64 le 16-03-2008 à 07:02:30

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 13-03-2008 à 17:53:48

Reply

Marsh Posté le 14-03-2008 à 14:13:13

En combien s'affiche la requete SQL de base ? Y-a-til les GROUP BY dedans ?

---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm

Reply

Marsh Posté le 14-03-2008 à 14:37:16

Bonjour,

Je n'ai pas compris se que tu me demande babasss ?

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 14-03-2008 à 14:40:46

Normal, je sais pas écrire une phrase....

En combien de temps s'affichait la requete SQL de base ? Y-a-til les GROUP BY dedans ?

---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm

Reply

Marsh Posté le 14-03-2008 à 14:48:12

Tu as essayé avec une sous requete :

Code :

SELECT u.user_level, u.user_color, u.user_group_id, u.user_flag, u.username, u.user_id AS USERID, 
  u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, 
  u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, 
  u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster, 
  ( SELECT COUNT(topic_id) FROM table_topics WHERE topic_poster = USERID GROUP BY topic_poster ) AS count_topic_nb
    FROM table_users u 
    WHERE u.user_id <> -1 
     AND u.username LIKE '%' 
    GROUP BY u.user_level, u.user_color, u.user_group_id, u.user_flag, u.username, u.user_id, u.user_viewemail, u.user_posts, 
  u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, 
  u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points
    ORDER BY u.user_regdate ASC 
    LIMIT 0, 30

Message édité par babasss le 14-03-2008 à 14:48:40

---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm

Reply

Marsh Posté le 14-03-2008 à 14:53:57

Ok donc,

La requête SQL de base, donc sans mes modifications, il n'y a pas les GROUP BY, la voici :

Code :

$sql = "SELECT username, user_id, user_viewemail, user_posts, user_regdate, user_from, user_website, user_email, user_icq, user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, user_allowavatar, user_allow_viewonline, user_session_time, user_points 
    FROM " . USERS_TABLE . "
    WHERE user_id <> " . ANONYMOUS . "$letter_sql
    ORDER BY $order_by";
//-- mod : flags ---------------------------------------------------------------
//-- add
    $sql = str_replace('SELECT ', 'SELECT user_flag, ', $sql);
//-- fin mod : flags -----------------------------------------------------------    
//-- mod : rank color system ---------------------------------------------------
//-- add
$sql = str_replace('SELECT ', 'SELECT user_level, user_color, user_group_id, ', $sql);
//-- fin mod : rank color system -----------------------------------------------

Et pour le temps d'exécution il est entre : ~~0,15s et 0,20s~~ 0,0287s
Donc autant qu'après ma modifications et avec "INNER JOIN"

Merci.

EDIT : Je me trompe, c'est plutôt 0,0287s et 0.0740s pour mon code modifié avec INNER JOIN

Code :

SELECT user_level, user_color, user_group_id, user_flag, username, user_id, user_viewemail, user_posts, user_regdate, user_from, user_website, user_email, user_icq, user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, user_allowavatar, user_allow_viewonline, user_session_time, user_points 
FROM table_users 
WHERE user_id <> -1 
AND username LIKE '%' 
ORDER BY user_regdate ASC LIMIT 0, 30

Je viens de tester ceci :

Code :

SELECT user_level, user_color, user_group_id, user_flag, username, user_id AS USERID, 
     user_viewemail, user_posts, user_regdate, user_from, user_website, user_email, 
     user_icq, user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, 
     user_allowavatar, user_allow_viewonline, user_session_time, user_points, 
     ( SELECT COUNT(topic_id) FROM table_topics WHERE topic_poster = USERID GROUP BY topic_poster ) AS count_topic_nb
       FROM table_users 
       WHERE user_id <> -1 
        AND username LIKE '%' 
       ORDER BY user_regdate ASC 
       LIMIT 0, 30

Ca a l'air de fonctionner, temps d'exécution 0,29s mais plus lourd quand même

Message édité par mkex64 le 14-03-2008 à 15:24:17

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 14-03-2008 à 15:26:17

Entre 0.29s et 40s, il y a de l'amélioration....
Il va forcément y avoir une charge supplémentaires en utilisant un système simple. Si tu as vraiment un problème de temps de réponse, tu peux faire une table agrégé qui te calcule tous les heures (via un CRON) le nb de post par id et tu fais une jointure avec cette table. Mais ce système est vraiment plus lourd...

Message édité par babasss le 14-03-2008 à 15:26:50

---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm

Reply

Marsh Posté le 14-03-2008 à 15:34:45

Je récapitule pour que ce soit plus clair :

- Requête de base, sans la colonne NB de sujets : Tps d'écution : 0,0287s

Code :

SELECT user_level, user_color, user_group_id, user_flag, username, user_id, user_viewemail, user_posts, user_regdate, user_from, user_website, user_email, user_icq, user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, user_allowavatar, user_allow_viewonline, user_session_time, user_points 
    FROM table_users 
    WHERE user_id <> -1 
    AND username LIKE '%' 
    ORDER BY user_regdate ASC LIMIT 0, 30

- Ma modifications de la requête pour ajouter la colonne NB de sujets, qui fonctionne presque entièrement car il ignore tout les membres ayant 0 topics (ce qui est normal) : Tps d'éxécution : 0.0740s
Donc c'est presque ça...

Code :

SELECT u.user_level, u.user_color, u.user_group_id, u.user_flag, u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster, COUNT(t.topic_id) AS count_topic_nb 
    FROM table_users u 
     INNER JOIN table_topics t 
       ON t.topic_poster = u.user_id 
    WHERE u.user_id <> -1 
     AND u.username LIKE '%' 
    GROUP BY u.user_level, u.user_color, u.user_group_id, u.user_flag, u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, u.user_points, t.topic_poster 
    ORDER BY u.user_regdate ASC 
    LIMIT 0, 30

- Même code mais avec LEFT JOIN, ça fonctionne mais le temps d'exécution passe à plus de 40 secondes. :pt1cable:

- Et la requête qui fonctionne le mieux pour le moment : mais quand même environ 10 fois plus lourde que la requêtes de base : temps d'exécution 0,29s :

Code :

SELECT user_level, user_color, user_group_id, user_flag, username, user_id AS USERID, 
        user_viewemail, user_posts, user_regdate, user_from, user_website, user_email, 
        user_icq, user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, 
        user_allowavatar, user_allow_viewonline, user_session_time, user_points, 
        ( SELECT COUNT(topic_id) FROM table_topics WHERE topic_poster = USERID GROUP BY topic_poster ) AS count_topic_nb
          FROM table_users 
          WHERE user_id <> -1 
           AND username LIKE '%' 
          ORDER BY user_regdate ASC 
          LIMIT 0, 30

Merci babasss je vais garder cette derniere requête, en attendant si je trouve mieux.

Message édité par mkex64 le 14-03-2008 à 15:35:56

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 14-03-2008 à 16:02:48

Par contre je viens de voir qu'avec cette dernière requête, si je trie par nombre de sujet, le temps d'exécution passe à 33 sec :

Code :

SELECT user_level, user_color, user_group_id, user_flag, username, user_id AS USERID, 
user_viewemail, user_posts, user_regdate, user_from, user_website, user_email, user_icq, 
user_aim, user_yim, user_msnm, user_avatar, user_avatar_type, user_allowavatar, user_allow_viewonline, 
user_session_time, user_points, 
( SELECT COUNT(topic_id) 
FROM table_topics 
WHERE topic_poster = USERID 
GROUP BY topic_poster ) AS count_topic_nb 
FROM table_users 
WHERE user_id <> -1 
ORDER BY count_topic_nb ASC 
LIMIT 0,30

Message édité par mkex64 le 14-03-2008 à 16:03:55

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 14-03-2008 à 16:18:04

Essaye de le faire en deux temps :

Créer une table temporaire avec le topic_id et le count topic_id
Dans ton SQL, faire le lien avec cette table...

---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm

Reply

Marsh Posté le 14-03-2008 à 16:18:04

Reply

Marsh Posté le 15-03-2008 à 12:01:57

Bonjour,

Ok je vais essayer de faire ça.

Merci.

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 15-03-2008 à 16:23:03

Bon voilà j'ai testé la table temporaire.
Les résultats sont OK mais le temps d'exécution est d'environ de 3 à 5 secondes.

J'ai fait ça comme requêtes :

Code :

CREATE TEMPORARY TABLE tmp_count_t ( 
count_topic_nb mediumint(8) UNSIGNED NOT NULL, 
topic_poster mediumint(8) DEFAULT '0' NOT NULL 
)

Code :

INSERT INTO tmp_count_t (count_topic_nb, topic_poster) 
SELECT COUNT(*), topic_poster 
FROM table_topics 
GROUP BY topic_poster

Code :

SELECT u.user_level, u.user_color, u.user_group_id, u.user_flag, 
u.username, u.user_id, u.user_viewemail, u.user_posts, u.user_regdate, 
u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, 
u.user_yim, u.user_msnm, u.user_avatar, u.user_avatar_type, 
u.user_allowavatar, u.user_allow_viewonline, u.user_session_time, 
u.user_points, t.topic_poster, t.count_topic_nb 
FROM phpbb_users u 
LEFT JOIN tmp_count_t t ON t.topic_poster = u.user_id 
WHERE u.user_id <> -1 
ORDER BY u.user_regdate ASC 
LIMIT 0, 30

Message édité par mkex64 le 15-03-2008 à 17:17:49

---------------
http://www.unicornis.org/

Reply

Marsh Posté le 15-03-2008 à 17:45:07

Si tu veux gagner un peu de temps, tu peux associer une date de rafraichissement pour que le calcul ne se lance que si cela fait plus de X temps que la table n'a pas été recrée...

---------------
Feedback : http://forum.hardware.fr/hfr/Achat [...] 2666_1.htm

Reply

Marsh Posté le 16-03-2008 à 07:00:59

Merci,

Finalement j'ai fait plus simple...

J'ai rajouté une colonne dans la table_users qui liste le nombre de topics pour chaque user.

J'ai intégré les données dans la table via un petit script qui lance des requêtes avec une boucle et tout ça...

Et j'ai rajouté dans les codes le fait que ça incrémente le nombre de topics d'un user à chaque fois que cet user en créé un.

Et ça marche à la perfection.

Merci à vous.

---------------
http://www.unicornis.org/

Reply

[Resolu] [phpBB] Ajouter colonne "Nb de sujets" dans memberlist.php

Sujets relatifs:

Leave a Replay