Mysql – How should I design a specific needs thesql table

MySQL

I am planning out a mysql table to record page views. None of my options seem very efficient. I plan to have the pages send a +1 update to the table when loaded. What I want to be able to do is query the table for view count for the last 30 days, or a different query for view count last seven days. The best plan I can come up with is to insert a new record for each view with a date/time stamp. This would require that each of the two possible queries (30 days or 7 days) to scan the whole table and use a conditional clause. I would also need to remove rows older than 30 days old with a cron job or something.
It seems that there should be a much more efficient way to do this.
I use freelancers to do my work and have learned from past experience that I need to be specific in how I want things done.
If you can suggest a better way to accomplish what I need in a more efficient way than I described please do so.

Best Answer

I suggest you create a table with the following fields:

  • Page URL
  • Date (Not datetime)
  • Count

And add a unique key on (page_url, date).

Then, use INSERT INTO .. ON DUPLICATE KEY UPDATE.

So, 

CREATE TABLE `page_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `page_url` varchar(512) NOT NULL,
  `date` date NOT NULL,
  `count` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_url` (`page_url`,`date`)
)

Then, use this insert:

INSERT INTO page_view (page_url, date) 
VALUES ('www.example.com/page1.html', date(now())) 
ON DUPLICATE KEY UPDATE count=count+1;
Related Question