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

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.

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

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

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

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

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

Bonjour, 

En effet, tu es un peu à coté  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

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

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

Fred