What is the best way to count page views in PHP/MySQL?

IMHO best solution is to have views_count stored inside memory (memcached, whatever), and do updates in memory. (Of course updates have to be synchronized)

Then you can use cron script which will push those values to db. (after some time - seconds, minutes, whatever.)


If you're interested in conserving resources and still using SQL for reporting, and precise # doesn't matter, you could try sampling like this (modify sample rate to suit your scale):

$sample_rate = 100;
if(mt_rand(1,$sample_rate) == 1) {
    $query = mysql_query(" UPDATE posts SET views = views + {$sample_rate} WHERE id = '{$id}' ");
    // execute query, etc
}

If memcache is an option in your server environment, here's another cool way to sample, but also keep up with the precise number (unlike my other answer):

function recordPostPageView($page_id) {
    $memcache = new Memcached(); // you could also pull this instance from somewhere else, if you want a bit more efficiency*

    $key = "Counter for Post {$page_id}";

    if(!$memcache->get($key)) {
        $memcache->set($key, 0);
    }

    $new_count = $memcache->increment($key);

    // you could uncomment the following if you still want to notify mysql of the value occasionally
    /*
    $notify_mysql_interval = 100;
    if($new_count % $notify_mysql_interval == 0) {
        $query = mysql_query("UPDATE posts SET views = {$new_count} WHERE id = '{$page_id}' ");
        // execute query, etc
    }
    */

    return $new_count;
}
  • And don't mind purists crying foul about Singletons. Or you could pass it into this function, if you're more purist than pragmatist :)

Tags:

Mysql

Php

Recent Posts