Most efficient way of storing daily page views, as well as a total count

php mysql performance pageviews storing-data
Adam picture Adam · Dec 1, 2012 · Viewed 8.9k times · Source

There allot of discussion on storing page views for an article or video in a database, but I can't seem to find any information on storing daily page views. For example DeviantArt shows you the past 15 or so days and how many page views each one got in a little graph, as well as the total page view for the profile.

Pageviews example

You can see above exactly what I'm trying to achieve, and DeviantArt do it on a huge scale as they get millions of hits.

I am using CodeIgniter on PHP and MySQL

Answer

Rob Farr picture Rob Farr · Dec 1, 2012

You could have a row for each day then increment the count when it is viewed.

INSERT INTO views (day,views) VALUES (CURDATE(),1) ON DUPLICATE KEY UPDATE views=views+1;

And a PHP script...

mysql_query("INSERT INTO views (day,views) VALUES (CURDATE(),1) ON DUPLICATE KEY UPDATE views=views+1;");

This uses a table called 'views' and 2 rows 'day' and 'views'.

See this article for more information.

Related questions

Doing calculations in MySQL vs PHP

Context: We have a PHP/MySQL application. Some portions of the calculations are done in SQL directly. eg: All users created in the last 24 hours would be returned via an SQL query ( NOW() – 1 day) There's a debate going on between …

Read More
PHP: Remote MySQL connections very slow

I have two servers, both running CentOS 5.7 and cPanel-CURRENT. One is x86 and the other is x64. Both are using Apache 2.2.21, PHP 5.3.8 and MySQL 5.1. If I query the local database on any one of the servers, the results are returned …

Read More
mysql_connect (localhost / 127.0.0.1) slow on Windows platform

I am using Windows 7, Apache 2, PHP 5, MySQL 5, all are on the same machine. I have found an interesting issue, I have the following code: $sql = "select * from user1"; $conn = mysql_connect("localhost", "root", "xxxxxxxx"); mysql_select_db("test1"); mysql_query("…

Read More