Révision de la structure des tables pour optimiser les requêtes – Moodle Learning Analytics by Isabelle Motte

Au vu de la structure des requêtes, je propose de créer une table qui enregsitre la liste des actions et leur nombre d’occurences et une table qui enregistre quel utilisateur a réalisé quelle action. Pour faciliter le nettoyage, je définit un champ « lasttime » dans la table des actions. Et j’en prévois un aussi dans la table des actions utilisateur, ce sera plus optimal que de stocker une ligne par action d’un utilisateur. Je prévois aussi d’intégrer une table dédiée au calcul des participants actifs par cours, pour voir comment cela impacte la requête. Pour l’instant, je teste juste l’intérêt de restructurer les données en créant de nouvelles tables et un script d’import des données depuis la table des logs standard.

Nouveau script d’import des données

$connexion = pg_connect("host=$host port=$port dbname=$dbname user=$username password=$password");

if (!$connexion) {
    echo "Erreur de connexion.\n";
    exit;
}

echo "Connexion réussie !";
  
    $sql ="INSERT INTO mdl_logstore_socialflow_hits_log
            (eventid, courseid, contextid, nbhits, lasttime)
        SELECT
            e.id AS eventid,
            l.courseid,
            l.contextid,
            COUNT(DISTINCT(l.userid)),
            MAX(l.timecreated) AS lasttime
        FROM mdl_logstore_standard_log l
        JOIN mdl_logstore_socialflow_evts e ON
            l.eventname = e.eventname
        WHERE l.timecreated > 1712755760
        GROUP BY eventid, courseid, contextid
        ORDER BY lasttime ASC";

     $result = pg_query($connexion,$sql);
     if ($result) {
        echo "Commande 1 exécutée avec succès.";
     } else {
         echo "Erreur lors de l'exécution de la commande 1 : " . pg_last_error($connexion);
     }

         $sql2 ="INSERT INTO mdl_logstore_socialflow_users_log
            (logid, userid, lasttime)
        SELECT 
            hl.id,
            sl.userid,
            MAX(sl.timecreated)
        FROM mdl_logstore_socialflow_hits_log hl, mdl_logstore_socialflow_log sl
        WHERE
            (sl.courseid=hl.courseid) AND (sl.contextid=hl.contextid) AND (sl.eventid=hl.eventid)
        GROUP BY hl.id,sl.userid";

     $result2 = pg_query($connexion,$sql2);
     if ($result2) {
        echo "Commande 2 exécutée avec succès.";
     } else {
         echo "Erreur lors de l'exécution de la commande 2 : " . pg_last_error($connexion);
     }

              
         $sql3 ="INSERT INTO mdl_logstore_socialflow_nbpa
            (courseid, nbpa)
        SELECT c.id,COUNT(DISTINCT(u.id)) AS nbpa
FROM mdl_user u
INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
INNER JOIN mdl_enrol e ON e.id = ue.enrolid
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid 
     AND ct.contextlevel = 50
INNER JOIN mdl_course c ON c.id = ct.instanceid 
     AND e.courseid = c.id
INNER JOIN mdl_role r ON r.id = ra.roleid 
     AND r.shortname = 'student'
WHERE e.status = 0 AND u.suspended = 0 AND u.deleted = 0 AND (ue.timeend = 0 OR ue.timeend > 1715347760) 
AND ue.status = 0
GROUP BY c.id";

     $result3 = pg_query($connexion,$sql3);
     if ($result3) {
        echo "Commande 3 exécutée avec succès.";
     } else {
         echo "Erreur lors de l'exécution de la commande 3 : " . pg_last_error($connexion);
     }
 
          
    pg_close($connexion);

Nouvelle requête pour obtenir le nombre de participants actifs dans un cours

SELECT nbpa FROM mdl_logstore_socialflow_nbpa WHERE id=2402;

Temps d’exécution 51ms

Pas vraiment d’intérêt pour un index puisqu’il y a un seul id de cours.
Je me demande si ce ne serait pas plus intelligent de faire de l’id de cours l’id unique.

Nouvelle requête pour obtenir les données du flux social

WITH event_hits AS (
    SELECT 
        hl.courseid, hl.contextid, hl.eventid, hl.nbhits
    FROM 
        mdl_logstore_socialflow_hits_log hl 
    WHERE 
        hl.courseid IN (3186, 4353, 2402, 3552, 1631) 
        AND hl.lasttime > 1714138160 
)
SELECT 
    ei.contextid, 
    ei.eventid, 
    ei.courseid, 
    evts.actiontype, 
    c.instanceid, 
    cm.instance, 
    m.name, 
    CASE 
        WHEN ei.courseid = 3186 THEN CAST(ei.nbhits AS NUMERIC) / 321 
        WHEN ei.courseid = 4353 THEN CAST(ei.nbhits AS NUMERIC) / 261 
        WHEN ei.courseid = 2402 THEN CAST(ei.nbhits AS NUMERIC) / 765 
        WHEN ei.courseid = 3552 THEN CAST(ei.nbhits AS NUMERIC) / 279 
        WHEN ei.courseid = 1631 THEN CAST(ei.nbhits AS NUMERIC) / 362 
    END AS freq 
FROM 
    event_hits ei
INNER JOIN 
    mdl_logstore_socialflow_evts evts ON ei.eventid = evts.id 
INNER JOIN 
    mdl_context c ON ei.contextid = c.id 
INNER JOIN 
    mdl_course_modules cm ON c.instanceid = cm.id 
INNER JOIN 
    mdl_modules m ON cm.module = m.id 
WHERE cm.visible = 1
ORDER BY 
    freq DESC 
LIMIT 
    10;

Temps d’exécution : 60ms !

Les index ne changent pas grand chose au temps d’exécution de la requête.

Nouvelle requête pour savoir si un étudiant a réalisé ou pas une activité

WITH table_logid AS (
    SELECT 
        hl.id
    FROM 
        mdl_logstore_socialflow_hits_log hl
    WHERE 
        hl.courseid=1631 AND hl.contextid=96643 AND hl.eventid=1
)
SELECT COUNT(ul.id)
FROM mdl_logstore_socialflow_users_log ul, table_logid 
WHERE ul.logid=table_logid.id AND userid=53745;

Avec un index sur les 3 colonnes du where de la table des hits et un index sur la colonne userid de la table des log utilisateur, la requête s’exécute en 35ms.

Nouvelle requête de nettoyage ses données

Evidemment l’encodage et le nettoyage des données sera plus complexe puisqu’il faut supprimer des lignes dans 2 tables, mais cette tâche tourne une fois par jour et peut être programmée la nuit. Donc on s’en fout.

SELECT id FROM mdl_logstore_socialflow_hits_log 
WHERE lasttime <= 1714742960;

Après, je fait un delete par morceau de toutes les lignes avec ces id dans la table des hits et un delete de toutes les lignes avec ce logid dans la table des log utilisateur.

Je dois encore trouver une stratégie pour nettoyer la table du nb de participants par cours. Mais je me demande si le plus simple ne serait pas de vider complètement la table pour tout recalculer mais il faut veiller à éviter les conflits d’écriture sur cette table (si à la fois un cron peut nourrir la table et le script qui affiche le flux social). Je vais creuser les bonnes pratiques concernant les tables de données temporaires.

Redondance et cohérence

Une brève discussion avec Raoul sur la question de la redondance d’information me rassure : ce qui compte, c’est surtout de préserver la cohérence de la base de données. Et dans mon cas, je peux facilement le faire en utilisant une contraintes de clés étrangères avec l’option ON DELETE CASCADE dans la table des log utilisateurs.

Tuto sur les clés étrangères avec l’option ON DELETE CASCADE

Conclusion des tests d’optimisation

L’affichage du flux social peut être rendu plus performant en cumulant plusieurs approches :

revoir la structure des données pour stocker les informations sur les hits et sur les actions utilisateurs dans 2 tables différentes
stocker les informations qui ne doivent pas être calculées à chaque requête (le nombre de participants actifs dans chaque cours) dans des tables de travail qui sont mises à jour une fois par jour via une tâche cron qui tourne la nuit
reformuler les requêtes pour les rendre plus optimales (via des requêtes imbriquées notamment)
placer des index sur les tables pour rendre les tris fréquents instantanés

La contrepartie est que le stockage des données sera un peu plus lent, tout comme le nettoyage des données. Mais on est en droit de penser que le coût en vaut la peine.

Temps de travail sur cet article et sur les tests : 1/2 jour