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

Question

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.

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 1

Usually what you do in these cases is have a logging table with 1 record per view. You have a cron run at certain intervals (daily) to summarize the logging data into another summary table. At the most basic level, you would have 1 record per day in the summary table with the total view count. Since the historical data never changes, you don't need to do updates. That gives you at most 365 records per item per year. Not a lot of records, so it scales well.

This scales better than having a single record for each day and updating the count. You could run into contention/locking issues if you are trying to update the same record multiple times. By doing an INSERT DELAYED and using a MyISAM table, your logging table can scale very well.

You can then select from the summary table to get the daily counts, and you could select from the logging table if you want to display the current days count.

If you want to keep your logging table small, when your cron run, you can rename the logging table and create a new, empty one. Then process the renamed logging table.

RENAME TABLE logging TO logging_old;CREATE TABLE logging LIKE logging_old;

If you really need to scale, you can use a replication trick to log views extremely fast. You use a BLACK HOLE table type for logging data, which replicates to another server where the table is MyISAM or InnoDB.