Réaliser un script de statistiques : vos conseils pour l'architecture de la table ?

Version imprimable

23/08/2006, 12h26
MaTHieU_

Réaliser un script de statistiques : vos conseils pour l'architecture de la table ?
Bonjour,

Je souhaite réaliser un petit script de statistiques en PHP/MySQL pour mon prochain site. Je souhaite entre autre pouvoir faire une requête qui affiche le nombre de personnes actuellement connectées (à 1 ou 2 minutes près).

Mon problème, je ne sais pas trop quelle architecture de table je dois mettre en place en sachant que je dois détecter si le visiteur est déjà passé sur le site le jour même, vérifier l'adresse IP, éventuellement tester un cookie, utiliser les sessions. Je veux pouvoir savoir pour un visiteur quelles pages il a consultées sur le site, d'où il vient et quelle navigateur il utilise.

Je pense à une architecture comme la suivante :
Code:

1 2 3 4 5 6 7 8 ID : int (autoincrement) DATE : timestamp IP : varchar(15) HOST : varchar(255) REFERER : varchar(255) USER_AGENT : varchar(255) PAGES : varchar(255)
Je vais essayer de l'améliorer via vos commentaires et conseils.

Merci d'avance,
Mathieu
23/08/2006, 15h02
Biglo

Salut,

Je n'ai pas énormément de conseils à te donner.

Pour l'adresse IP, je te conseille de la stocker en tant qu'entier. Ca prendra beaucoup moins de place et surtout un index sur cette colonne sera bien plus performant. MySQL propose les fonctions INET_NTOA() et INET_ATON() qui permettent de transformer une adresse IP en notation pointée sous forme numérique et inversement.

Il ne doit pas exister énormément de User_Agent. Peut-être pourrais-tu créer une table "UserAgent" et y stocker les principaux. Ainsi dans les infos du visiteur, tu pourrais y placer un TINYINT ou un SMALLINT, toujours dans l'esprit de gagner de la place.

Sous quelle forme penses-tu stocker le champ "Pages" ? Je vois que tu lui as donné le type "chaîne", mais je ne vois pas comment tu comptes l'utiliser.

Pourquoi un timestamp pour la date ? Que représente-t-il ? La date de la dernière visite (page demandée) ? J'aurais pensé que la date du jour t'aurait suffit (donc type DATE) et j'aurais mis une contraine d'unicité pour (IP, DATE).

Bien sûr, je ne saispas quelles informations te seront utiles. Par exemple, si un même visiteur vient sur la page "index" avec le referer "google". Mais un peu plus tard dans la journée, il est possible qu'il revienne avec un autre referer. Veux-tu garder les deux ?

Voilà, je pense qu'un peu plus de détails de ta part serait le bienvenu. Avec éventuellement une mise en situation et un petit jeu d'essai.
23/08/2006, 15h26
MaTHieU_
Bonjour,

Alors voici des détails comme demandé :

Citation:

Envoyé par Biglo

Pour l'adresse IP, je te conseille de la stocker en tant qu'entier.

En effet, je ne connaissais pas cette fonction ;)

Citation:

Envoyé par Biglo

Il ne doit pas exister énormément de User_Agent.

Si justement, il en existe énormément, cette données, je souhaite la garder, c'est à partir d'elle que je vais déterminer le navigateur et l'OS.

Citation:

Envoyé par Biglo

Par exemple, si un même visiteur vient sur la page "index" avec le referer "google". Mais un peu plus tard dans la journée, il est possible qu'il revienne avec un autre referer. Veux-tu garder les deux ?

Oui, je souhaite garder les deux.

Citation:

Avec éventuellement une mise en situation et un petit jeu d'essai.

Je vais donc essayer de donner un exemple :
- Première visite, on enregistre les informations du visiteur (sessid, ip, host, referer, useragent)
- Parcours du site, on enregistre les pages vues et le tempe entre chaques pages
- Deuxième visite, même jous, même IP ou cookie, on créé un nouvel enregistrement (en effet, la personne peut avoir changé de navigateur, utilisé un autre moyen pour arriver sur le site, etc.)
Ce qui est unique à un moment T :
- IP / HOST
- SESSID
Ce qui peut changer :
- REFERER
- USER_AGENT
- PAGE CONSULTEE
Il faut que je trouve une méthode pour détecter et regrouper les informations du visiteur qui vient plusieurs fois dans la même journée. Ce que l'on appelle "visite unique". Celle-ci ne peut se faire que par l'ip ou le cookie je pense.

J'attend vos commentaires pendant que je prépare une structure améliorée grâce à tes conseils.

Merci pour votre aide,
Mathieu
23/08/2006, 16h11
Biglo
Citation:

Envoyé par MaTHieU_

Si justement, il en existe énormément, cette données, je souhaite la garder, c'est à partir d'elle que je vais déterminer le navigateur et l'OS.

En fait, ce que je propose, ça serait d'analyser le user agent et ne pas le stocker tel quel dans la table. On pourrait par exemple imaginer une table "navigateur" (mozilla, IE, firefox, opera, ...), une table système d'exploitation et dans ta table d'infos sur le visiteur, tu aurais deux clés étrangères pour l'OS et le navigateur + des champs pour la version par exemple.

Ca te permettrait de gagner déjà pas mal de places. Car si tu stockes l'user agent pour chaque visiteur, la taille va "exploser" sur les sites très fréquentés. Par exemple, si on compte une moyenne de 50 octets par user agent, et 10000 visiteurs par jour : tous les mois, ta table augmentera de 14Mo juste pour cette info.

J'insiste beaucoup sur la taille de la base, mais ça me semble réellement important pour ce genre d'applications.
Citation:
Envoyé par MaTHieU_

Je vais donc essayer de donner un exemple :

Première visite, on enregistre les informations du visiteur (sessid, ip, host, referer, useragent)
Parcours du site, on enregistre les pages vues et le tempe entre chaques pages
Deuxième visite, même jous, même IP ou cookie, on créé un nouvel enregistrement (en effet, la personne peut avoir changé de navigateur, utilisé un autre moyen pour arriver sur le site, etc.)
Comment comptes-tu distinguer une deuxième visite d'une visite qui se continue ? Session fermée = fin de visite ?

Citation:

Envoyé par MaTHieU_

Il faut que je trouve une méthode pour détecter et regrouper les informations du visiteur qui vient plusieurs fois dans la même journée. Ce que l'on appelle "visite unique". Celle-ci ne peut se faire que par l'ip ou le cookie je pense.

Si ce n'est pas déjà fait, il faut également que tu penses au cas de plusieurs utilisateurs d'un même réseau qui visitent le site. Il risque donc d'y avoir plusieurs couples (sessId, ip) en même temps.
23/08/2006, 16h28
MaTHieU_

Bonjour,

Citation:

Envoyé par Biglo

En fait, ce que je propose, ça serait d'analyser le user agent et ne pas le stocker tel quel dans la table. On pourrait par exemple imaginer une table "navigateur" (mozilla, IE, firefox, opera, ...), une table système d'exploitation et dans ta table d'infos sur le visiteur, tu aurais deux clés étrangères pour l'OS et le navigateur + des champs pour la version par exemple. Ca te permettrait de gagner déjà pas mal de places. Car si tu stockes l'user agent pour chaque visiteur, la taille va "exploser" sur les sites très fréquentés. Par exemple, si on compte une moyenne de 50 octets par user agent, et 10000 visiteurs par jour : tous les mois, ta table augmentera de 14Mo juste pour cette info.

C'est exact avec quand même un champ USERAGENT en VARCHAR(255) où on ne stockera que les USERAGENT qui après traitement ne corespondent pas à un navigateur ou OS référencé afin de faire évoluer la liste facilement.

Citation:

Envoyé par Biglo

Comment comptes-tu distinguer une deuxième visite d'une visite qui se continue ? Session fermée = fin de visite ?

Pour moi, ce qui semble le plus facile à faire est : session fermée = fin de visite. Le sessid étant normalement unique, c'est lui qui peut même me permettre de gérér mes visiteurs en utilisant cette information comme clef unique dans ma table principale ?

Citation:

Envoyé par Biglo

Si ce n'est pas déjà fait, il faut également que tu penses au cas de plusieurs utilisateurs d'un même réseau qui visitent le site. Il risque donc d'y avoir plusieurs couples (sessId, ip) en même temps.

D'où l'intérêt de travailler avec des sessions, je pense.

Je vais vous poster en soirée une architecture sur deux tables avec quelques explications. Vous pourrez alors me donner vos avis et conseils sur quelque chose d'un peu plus concret.

Merci,
Mathieu
23/08/2006, 16h48
Biglo

Citation:

Envoyé par MaTHieU_

C'est exact avec quand même un champ USERAGENT en VARCHAR(255) où on ne stockera que les USERAGENT qui après traitement ne corespondent pas à un navigateur ou OS référencé afin de faire évoluer la liste facilement.

Oui pourquoi pas. Et dès que tu auras traité ce nouvel user agent, il suffira de mettre à jour les clés étrangères et de remettre à NULL le useragent.

Citation:

Envoyé par MaTHieU_

Pour moi, ce qui semble le plus facile à faire est : session fermée = fin de visite. Le sessid étant normalement unique, c'est lui qui peut même me permettre de gérér mes visiteurs en utilisant cette information comme clef unique dans ma table principale ?

Je me suis également posé la question. Mais théoriquement, un numéro de session peut être réutilisé dans le futur. Donc le numéro de session n'est pas unique dans le temps. Je ne sais pas s'il y a une seule clé primaire naturelle. Le couple (idSess, IP) est déjà mieux mais pas tout à fait unique, même si la probabilité d'avoir le même couple est vraiment faible.

Bonne continuation
23/08/2006, 16h57
MaTHieU_

Bonjour,

Citation:

Envoyé par Biglo

Oui pourquoi pas. Et dès que tu auras traité ce nouvel user agent, il suffira de mettre à jour les clés étrangères et de remettre à NULL le useragent.

Oui, c'est exactement ce que je pense faire.

Citation:

Envoyé par Biglo

Mais théoriquement, un numéro de session peut être réutilisé dans le futur.

Je crois que c'est un numéro unique le sessid (calculé via un timestamp), mais je ne suis pas sûr, j'ai donc cherché un peu sur Google mais j'ai pas encore trouvé confirmation ou infirmation.

Mathieu

Bonjour,

Avec un peu de retard, voici la structure de la table statistiques :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE `statistiques` ( `id` int(11) NOT NULL auto_increment, `sessid` varchar(32) NOT NULL, `ip` int(11) NOT NULL, `host` varchar(255) default NULL, `referer` varchar(255) default NULL, `navigateur` int(11) default NULL, `os` int(11) default NULL, `useragent` varchar(255) default NULL, `dernier` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `premier` timestamp NOT NULL default '0000-00-00 00:00:00', `horaire` text NOT NULL, `page` text NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Le fonctionnement est le suivant :

Lors du premier accès du visiteur (vérification de l'existance de la session), on utilise une requête du type INSERT où l'on renseigne les champs suivants : sessid, ip, host, referer, navigateur, os, useragent, dernier, premier, horaire et page
Pour la consultation des autres pages (vérification de l'existance de la session), nous utilisons une requête du type UPDATE où nous alons mettre à jour les champs suivants : dernier, horaire et page

Les champs premier et dernier permettent respectivement de savoir combien de visites il y a eu à une date ou un interval de temps donné et le nombre de visiteurs connectés sur le site.

Qu'en pensez-vous ?

Merci,
Mathieu

26/08/2006, 00h41
Biglo

Salut,

Peux-tu donner un exemple de requête UPDATE que tu réaliseras lors d'une visite ?

Finalement, est-ce que sessid est vraiment nécessaire dans la bdd ? Car je suppose que dès que tu auras inséré le visiteur, tu feras quelque chose du genre $_SESSION['idVisiteur'] = x et que tu réutiliseras ce id pour faire l'UPDATE. Et je ne vois pas vraiment ce que le numéro de session apporte aux statistiques.

Pour navigateur et OS, un TINYINT (voire SMALLINT) non signé sera suffisant car je doute que tu aies + de 256 enregistrements (ou 64k). En tout cas, n'oublie pas le UNSIGNED pour tes clés car sinon la moitié de l'espace occupé est inutilisable (avec l'auto increment du moins).

Tu ne m'avais pas répondu à propos de l'utilisation de la colonne "Page". Tu comptes concaténer la page visitée à chaque fois ? Si c'est le cas, ne penses-tu pas que cette donnée sera difficilement exploitable ? Par exemple, si tu veux afficher le nombre de visites de la page "Accueil" pour le 20 août.

Ne serait-il pas plus simple de créer une table PageVisitée où tu stockerais l'id du visiteur, la date/heure de la visite de la page et un identifiant de la page. Cette méthode solutionnerait un problème : un visiteur arrive sur le site à 23h50. Avant minuit, il regarde plusieurs pages. A 0h05, il regarde une autre page. Vu que "dernier" sera à jour, comment sauras-tu que les autres pages ont été visitées le jour précédent ? Grâce à la colonne horaire ? (je n'ai pas trop compris à quoi elle allait servir). Si c'est le cas, arriveras-tu à utiliser facilement cette donnée ?

Bonsoir,

Je suis actuellement en train de travailler dessus :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 <?php session_start(); if( !isset($_SESSION['page']) ) { // nouveau visiteur $_SESSION['horaire'] = time(); $_SESSION['page'] = $_SERVER['REQUEST_URI']; $_SESSION['ip'] = GetIP(); $_SESSION['host'] = gethostbyaddr($_SESSION['ip']); $sql = "INSERT INTO statistiques(sessid,ip,host,dernier,premier,horaire,page) "; $sql .= "VALUES('".session_id()."',INET_ATON('".$_SESSION['ip']."'),'".$_SESSION['host']."',NOW(),NOW(),'".$_SESSION[horaire]."','".$_SESSION['page']."')"; echo $sql; } else { // nouvelle page consultée $_SESSION['page'] .= ';'.$_SERVER['REQUEST_URI']; $_SESSION['horaire'] .= ';'.time(); $sql = "UPDATE statistiques SET horaire='".$_SESSION['horaire']."' AND page='".$_SESSION['page']."' WHERE sessid='".session_id()."'"; echo $sql; } ?>
Citation:

Finalement, est-ce que sessid est vraiment nécessaire dans la bdd ? Car je suppose que dès que tu auras inséré le visiteur, tu feras quelque chose du genre $_SESSION['idVisiteur'] = x et que tu réutiliseras ce id pour faire l'UPDATE. Et je ne vois pas vraiment ce que le numéro de session apporte aux statistiques.

En effet, j'avais pas pensé à ça !

Je suis en train de réfléchir pour le navigateur et l'os...

Merci,
Mathieu