Marqueur de visites, mysql ou fichier textes?

Bonjour,

Je souhaiterais rajouter dans mes sites un petit système de statistiques. L'idée n'est pas de faire des statistiques complètes, cpanel et tout les outils qui vont avec le font très bien.

Ce que je voudrais c'est générer un tableau des visiteurs et une liste de leur provenance, pas plus pas moins, c'est je trouve largement suffisant.

Pour la visualisation des résultats, je n'est pas de problème particulier, par contre c'est le "marqueur" qui me préoccupe un peu plus.
En effet je voudrais que ce marqueur soit le plus rapide possible afin de ralentir au minimum la page.

Voilà ce que j'ai écrit pour l'instant :

if(!isset($_SESSION['statistic'])){
    		  
      $_SESSION['statistic'] = true;
      $date = date('Y-m-d',time()); 
    
      if (preg_match(',google|yahoo|msnbot|crawl|lycos|voila|slurp|jeeves|antibot|nutch|ia_archiver|scooter|fluffy|teoma,i', 
      $_SERVER['HTTP_USER_AGENT'])) return;
		 
      $today = date('Y-m-d',time());
      $result = mysql_query("SELECT id FROM statistics WHERE date='$date'");
      list($same_day) = mysql_fetch_row($result);
      if($same_day){
        mysql_query("UPDATE statistics SET total=total+1 WHERE date='$date'");
      }else{
        mysql_query("INSERT INTO statistics (date, total) VALUES ('$date', '1')");
      }

      $referer = (isset($_SERVER['HTTP_REFERER']))? $_SERVER['HTTP_REFERER'] : '';
      if($referer){
        $result = mysql_query("SELECT id FROM referers WHERE domain='$referer'");
        list($referer_id) = mysql_fetch_row($result);
        if($referer_id){
          mysql_query("UPDATE referers SET total=total+1 WHERE id='$referer_id'");      
        }else{
          mysql_query("INSERT INTO referers (domain, total) VALUES ('$referer', '1')");
        }
      }
    
    }

j'ai utilisé les sessions pour filtrer les utilisateurs déjà présents des nouveaux, je pense que c'est pas mal, ça évite de faire tourner le script pour rien si l'utilisateur est déjà là.

Par contre, même si ce n'est que a la première connections j'ai quand même 4 requêtes mysql, et je doute un peu du domain='$referer' qui doit être un peu lourd sur un table qui peu vite grandir.

Je me demandais donc si ce n'est pas plus performant pour la partie referer d'utiliser des fichiers textes. Ou bien si il y d'autres méthodes plus appropriées.
Modifié par matmat (04 Jun 2008 - 01:22)

Hello
Il faut tout d'abord savoir qu'une base de donnée est faite pour un grand nombre de requêtes en un court laps de temps (si tu peux choisir ta BDD, il y en a sûrement des plus rapides que d'autre) alors que les opérations sur un fichier texte sont beaucoup plus lentes (quelques-unes par minutes, si je me souviens bien).

Ensuite, pour ne pas avoir toutes ces requêtes sur la même page et gagner un peu de temps, il suffit de regrouper les requêtes du même type (alors qu'actuellement, tu as une requête SELECT, une UPDATE et une INSERT pour chaque table).

Il faut aussi savoir que le referer n'est pas une valeur sûre (il peut ne pas être renseigné, et certains navigateurs permettent de le modifier).
Pour finir, si tu as des données que tu peux organiser en tableau, utilise une base de donnée : C'est beaucoup plus rapide et efficace qu'un fichier texte. Et même si c'est un long texte (comme un referer). Il ne faut pas avoir peur que ta BDD grandisse vite, elle est là pour ça (tu peux aussi la nettoyer de temps en temps, ou faire un script qui le fait automatiquement si tu ne veux pas qu'elle grandisse trop).

Concernant la structure de ton compteur, je pense que tu pourrais t'épargner quelques lignes de code en le simplifiant :
Une seule table pour le compteur, et une ligne pour chaque visiteur. Cette ligne contiendrait une colonne date et une colonne referer. A chaque nouveau visiteur, une ligne est ajoutée dans la table.
Pour les totaux, il te suffirait donc simplement de compter (à l'aide d'une fonction MySQL toute simple : COUNT()) le nombre de lignes pour avoir le nombre de visiteurs, le nombre de lignes dans la colonne date, en les regrouppants par date (GROUP BY) pour avoir le nombre de visiteur par date, et idem pour la colonne referer pour avoir le nombre de visiteurs ayant un même referer. Il me semble que c'est là où tu voulais en arriver avec tes colonnes total.
Modifié par superjun (08 Jun 2008 - 00:52)

Merci de ta réponse superjun!

a écrit :
Ensuite, pour ne pas avoir toutes ces requêtes sur la même page et gagner un peu de temps, il suffit de regrouper les requêtes du même type (alors qu'actuellement, tu as une requête SELECT, une UPDATE et une INSERT pour chaque table).

Tu as raison je vais essayé de travailler comme ça.

a écrit :
Il ne faut pas avoir peur que ta BDD grandisse vite, elle est là pour ça (tu peux aussi la nettoyer de temps en temps, ou faire un script qui le fait automatiquement si tu ne veux pas qu'elle grandisse trop).

a écrit :
Concernant la structure de ton compteur, je pense que tu pourrais t'épargner quelques lignes de code en le simplifiant :
Une seule table pour le compteur, et une ligne pour chaque visiteur. Cette ligne contiendrait une colonne date et une colonne referer. A chaque nouveau visiteur, une ligne est ajoutée dans la table.

Cette solution est plus rapide au niveau du marqueur, mais c'est vrai que j'ai un peu peur que ma base de donnée grandisse trop vite, imaginons pour 2000 visites par jour X 2 ans on arrive à deux millions d'entrées. Je suis un peu ignorant en performance mysql, mais il me semble que si je veux récuperer tout mes referers sur 2 millions d'entrées même avec des GROUP BY ça peux être un peu rude.

C'est pour ça que j'ai eux l'idée du total par nombre de jours ce qui m'oblige par contre à mettre les referers sur une autre table.

C'est effectivement assez logique, mais il faut aussi penser au fait qu'il peut très bien y avoir 2 millions de referers différents dans ta table. Et même si c'est 'seulement' un million, imagine que MySQL doive faire une recherche parmi 1 million de referers à chaque fois qu'un nouveau visiteur arrive...
Une recherche par date n'est pas très gourmande, même sur 2 millions d'enregistrements (puisque logiquement, les dates se suivent) : MySQL a un attribut de ligne qui permet d'optimiser ça. Il suffit de cocher l'attribut Index pour la colonne date.
D'ailleurs, il me semble que ça aiderait aussi pour les recherches sur le referer. (Pour plus d'infos sur le fonctionnement : http://dev.mysql.com/doc/refman/5.0/fr/mysql-indexes.html )

De toute manière, si c'est pour afficher de temps en temps (pour toi-même) ces infos, c'est plus utile d'avoir une rapidité accrue lors de l'enregistrement dans la BDD quand le visiteur entre sur le site. Toi, tu pourras bien souffrir un petit ralentissement lors de la consultation des statistiques.
Et si c'est pour un compteur qui est affiché sur le site, donc qui doit être actualisé à chaque nouveau visiteur, je pense que la structure que j'ai proposé vaut bien la tienne (le temps que je perds lors du comptage des lignes, tu le perds lors de la recherche d'un referer ou d'une date).
Si c'est pour tes statistiques personnelles, tu peux aussi utiliser les services de Google Analytics qui sont gratuits et bien fournis

a écrit :
C'est effectivement assez logique, mais il faut aussi penser au fait qu'il peut très bien y avoir 2 millions de referers différents dans ta table. Et même si c'est 'seulement' un million, imagine que MySQL doive faire une recherche parmi 1 million de referers à chaque fois qu'un nouveau visiteur arrive...

ça c'est peu probable, vu que quand un visiteur arrive le marqueur teste si le referer existe déja et si il existe déja il incrémente la colone total, donc il faudrait qu'il y est un million de referers différents pour que l'on est un table d'un million d'entrées. par contre c'est vrai que la table peut tout de même grandir vite, chaque mot clés différents sur google par exemple générant un referer différent.

a écrit :
De toute manière, si c'est pour afficher de temps en temps (pour toi-même) ces infos, c'est plus utile d'avoir une rapidité accrue lors de l'enregistrement dans la BDD quand le visiteur entre sur le site. Toi, tu pourras bien souffrir un petit ralentissement lors de la consultation des statistiques.

Même si les statistiques ne seront pas seulement pour moi, je suis complètement d'accord avec cette logique.

a écrit :
Et si c'est pour un compteur qui est affiché sur le site, donc qui doit être actualisé à chaque nouveau visiteur, je pense que la structure que j'ai proposé vaut bien la tienne (le temps que je perds lors du comptage des lignes, tu le perds lors de la recherche d'un referer ou d'une date).

Ce n'est pas pour un compteur, c'est pour informer, dans une interface d'administration, du nombre de visites par jour sous forme graphique et des referers classés par domaines. Et c'est justement le point qui me pose problème par rapport à ta solution. Imaginons que j'ai au bout de deux ans deux millions d'entrées sur ma table, j' aurais deux millions de referers, donc multiplié par 100 octets par lignes 200 Mega d'octets à traiter (je fais plusieurs boucles pour les associés par sites puis récupérer les motos clés q=, p= etc..) pour afficher ma liste de referers.
Modifié par matmat (09 Jun 2008 - 16:36)

Salut,
Si tu n'as pas besoin d'avoir des stats en temps réel, tu peux te servir des logs du serveur (apache). Par exemple une fois par jour, un tache cron tourne pour analyser les logs de la journée passée, et stocker ce qu'il faut dans la BDD.

http://russell.dyerhouse.com/cgi-bin/article.cgi?article_id=78
Modifié par SuD (09 Jun 2008 - 17:49)

C'est vrai que si des marqueurs existe déjà sous forme de logs Apache, le plus simple est de les utiliser! Sauf que je n'arrive pas à les lire à cause de problèmes de permissions ;

Pour le problème des index et referers, je vais organiser ma table referers ainsi :

id_referer | domain | url | domain_total | url_total

domain c'est le domaine comme www.google.fr , de le stocker va me permettre d'optimiser les select et les update . En effet d'après la lecture du document sur les index Mysql, si mysql trouve un des index sur deux il ne recherche le deuxième index que dans ceux ainsi selectionnés.

L'autre avantage c'est que je pourrais grâce à l'information domain_total, organiser l'affichage des referers par ordre décroissant du domaine d'ou viennent le plus de visites.

Cela fera encore un requête supplémentaire pour le marqueur, mais cela n'a pas d'importance car je vais utiliser javascript. Je n'aurais qu'a mettre le code suivant en bas de mes pages :

send_stat();

la fonction send_stat();

function send_stat(){

  if(!isset($_SESSION['statistic'])){
  
    $_SESSION['statistic'] = true;
    
    $write = "<script type='text/javascript'>";
    $write .= "addEvent(window,'load',function(){";
    $write .= "if(document.referrer){";
    $write .= "var xhr = new ajaxRequest('stats.php?');";
    $write .= "xhr.post('referers='+escape(document.referrer));";
    $write .= "};";
    $write .= "});";
    $write .= "</script>";
    echo $write;
    
  }
  
}

Comme le traitement se fera grâce à Ajax, seulement si c'est la première visite et une fois que la page est chargé, le ralentissement devrais vraiment être minime.
Modifié par matmat (10 Jun 2008 - 21:31)