Partage
  • Partager sur Facebook
  • Partager sur Twitter

Table monstrueuse ! Comment l'optimiser

    27 mai 2021 à 11:31:04

    Bonjour, 

    J'ai mis en place depuis un an une table de view sur mon site de livre . Le principe est simple, comptabiliser chaques vues pour éviter de la compter deux fois. Mon problème ... elle fait actuellement 250 MILLIONS de ligne !!!! et ceux en un an :'( 

    ça fait un petit temps que je me dis que je dois faire quelque chose mais la je pense que c'est VRAIMENT necessaire, seul problème, je ne sais pas comment faire pour l'alléger sans supprimer les vues qui s'y trouve.

    Voila la structure de ma table:

    la colonne ip et user_id sont la car j'enregistre les vues des utilisateurs connectés et anonymes sinon se serait trop simple de d'ajouté des vues aux livres.

    la colonne entity_id et entity_type servent à savoir quelle ressource a été consulté car cela peut être des livres ou des chapitres.

    Il y a une option sur le site de voir quel chapitre a été lu ou non, cochable ou décochable a volonté.

    la colonne parent_id est fait pour les chapitres et retrouvé facilement les vues de tous les chapitres d'un livre.

    Pour le moment une ligne correspond a une vue ... je me demandé s'il y avait moyen d'optimiser cela ? J'ai vu que l'on pouvait partitionner des tables, est-ce une solution ?

    En écrivant ce post je me suis dis que je pourais déja commencé par séparer les vues livres et chapitres ?

    Merci d'avance

    Fred

    Précision : Cette table n'est la que pour être sur que l'on ne comptabilise pas une vue deux fois, je ne fais pas des counts sur cette table car se serais ingérable. J'ai un champ view dans ma table livre que j'incrémante des que j'insère une view dans la bdd.

    -
    Edité par methos0 7 juin 2021 à 20:35:25

    • Partager sur Facebook
    • Partager sur Twitter
      27 mai 2021 à 12:01:09

      Bonjour,

      250 millions de vues ! C'est digne de Facebook !

      Une première piste serait de regrouper les vues "identiques" (même ressource même utilisateur).

      Que retourne cette requête :

      SELECT
      	user_id,
      	entity_id,
      	entity_type,
      	COUNT(*) AS nombre
      FROM matable
      GROUP BY
      	user_id,
      	entity_id,
      	entity_type
      HAVING COUNT(*) > 1;

      Si cette requête ressort de très nombreuses lignes, l'idée serait de rajouter une colonne "nombre_vues" dans ta table, et au moment où tu enregistres une vue, avant de créer une nouvelle ligne tu vérifies si il n'existe pas une ligne "identique" et tu incrémentes le nombre_vues, sinon tu insères.

      Si tu es partant, tu pourras supprimer les pseudo-doublons et incrémenter la colonne "nombre_vues" à partir de ma requête précédente.

      • Partager sur Facebook
      • Partager sur Twitter
      Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
        27 mai 2021 à 12:42:12

        J'irais pas jusque facebook ^^ mais il y a un peu de passage, pour cela que je dois optimisé cela :p

        Chaque vue est unique :'( je n'insert une vue que si elle ne se trouve pas dans la table. Je regarde l'utilisateur et l'ip avant d'inserer. J'ai une column view dans la table livre et chapitre qui s'incrémente à chaque insertion. Cela évite de faire des count a tout bou de champ ^^

        Désolé pour le manque de précision :s

        J'ai fait ta requête mais mysql n'a pas apprécié et a planté, c'est pour cela que j'essaie de faire quelque chose car les requêtes un peu complexes plantes ...

        Merci d'avance

        Fred

        • Partager sur Facebook
        • Partager sur Twitter
          27 mai 2021 à 13:48:06

          Du coup je ne comprends pas ... Si chaque ligne est unique, il n'y a pas vraiment de solution ...

          Tu veux compter des vues ... donc une ligne par vue reste nécessaire ...

          Après tu peux peut-être regrouper par utilisateur et ne stocker que la dernière IP plutôt que de tout garder ...

          • Partager sur Facebook
          • Partager sur Twitter
          Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
            27 mai 2021 à 14:34:04

            je ne veux pas compter des vues ^^, les livres et les chapitres ont des colonnes view qui s'incrémente a chaque insertion donc je n'ai absolument pas besoin de compte les vues dans la table view, se serait une horreur d'ailleurs. Mais chaque insertion, chaque requête commence à être une horreur malgré les index. Je ne parle même pas des backup, ce que j'aimerais c'est alleger cette table. 

            le seul interré de cette table et d'être sur de ne pas incrémente le compteur de vue des livres ou chapitres si quelqu'un visite une ressource, voila le process :

            L'utilisateur visite le livre/ressource 

            Je check si l'id_user ou l'ip est presente dans la table

            Si ce n'est pas le cas, j'insert une nouvelle ligne dans la table view et j'incrémente le compteur de la ressource

            Ce que je veux opti c'est la façon de cheker si l'utilisateur à déjà visité ou nom de ressource. J'avais pensé stoké les vues par livre avec une column ids_chapitre qui serait un array d'id sous forme de string

            L'idée serais de récupéré et mettre en session les vues d'un livre et de le mettre en session. Il suffirait de faire un simple in_array , mais certain livre (populaire) contiennent plus de 200 chapitres ... donc je ne sais pas si c'est vraiment opti ....

            Ma table serais plus légère car j'aurais beaucoup moins de ligne car 99% des lignes sont des chapitres, mais le traitement PHP derrière serais plus gros ... 

            -
            Edité par methos0 27 mai 2021 à 14:35:24

            • Partager sur Facebook
            • Partager sur Twitter
              2 juin 2021 à 20:02:58

              Bonjour,

              Je viens juste de sortir d'un cours pour découvrir le PHP et MySQL, donc je vais essayer d'apporter une idée logique plus que technique.

              De ce que je comprends ta table permet de savoir le nombre de vu qui a était fait par chapitre sur chacun des livres et c'est là le seul intérêt.

              Pourquoi ne pas faire une requête qui te sort dans un premier temps le nombre de vu par chapitre, tu conserves le résultat (par exemple le livre Eragon chapitre 1 a 5000 vue). Tu fais une copie de ta base au cas où et tu supprimes les données de ta table (les 250 millions de lignes).

              Puis tu utilises les données du nombre de vue par chapitre pour faire le référentiel de base des vues des chapitres (un nombre stocké en dur). Lors du calcul du nombre de vue tu prend le nombre en dure + les nouvelles lignes qui arrivent dans ta table remise a 0.

              En espérant que je ne sois pas a côté de la plaque ^^

              • Partager sur Facebook
              • Partager sur Twitter
                7 juin 2021 à 20:39:11

                Bonjour, 

                En effet, tu es un peu à coté :euh: mais tu je te remercie vraiment d'avoir participé. J'ai fait une précision dans mon posts initiale.

                Personne pour la solution que j'ai proposé ?

                Merci d'avance 

                Fred

                • Partager sur Facebook
                • Partager sur Twitter
                  8 juin 2021 à 12:03:47

                  Je reviens sur le sujet sans trop de proposition ... Pour identifier si une vue est "unique", tu as besoin de stocker TOUTES les vues précédentes, donc l'utilisateur (ou l'IP) et l'élément consulté, éventuellement avec la date ... Je ne vois pas comment faire autrement ...

                  Mettre en place une table de relation (comme évoqué au début) entre utilisateur et chapitre te permettrait limiter la table des vues aux utilisateurs anonymes, je ne sais pas quel est la proportion dans la table actuelle d'enregistrements sans user_id ... Si elle est assez faible (moins de 40% par exemple) alors tu pourras gagner en volumétrie.

                  En complément, faire des tables spécifiques pour les vues anonymes, par type de contenu (vue_livre, vue_chapitre, etc.) pourrait encore réduire la taille de la table actuelle ... Mais au prix d'une gestion différenciée côté applicatif ...

                  Mais globalement, si ton site génère autant de trafic (250 000 000 c'est énorme) tu ne peux échapper au succès ...

                  Enfin, une solution qui devrait être plus performante à volume égal serait de travailler en NoSQL juste pour ce stockage là : un document "vues" où tu stockes TOUTES les vues comme tu le fais actuellement, mais sans les contraintes d'intégrité ...

                  • Partager sur Facebook
                  • Partager sur Twitter
                  Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL
                    17 juin 2021 à 14:25:24

                    Merci pour vos réponses, mon associé prèfere plancher sur un nouveau design donc le sujet est en suspend pour le moment, une fois le design fini, je donnerais les resusltats de mes essais :D

                    Fred

                    • Partager sur Facebook
                    • Partager sur Twitter

                    Table monstrueuse ! Comment l'optimiser

                    × Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
                    • Editeur
                    • Markdown