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.
I suggest you create a table with the following fields:
- Page URL
- Date (Not datetime)
And add a unique key on (page_url, date).
INSERT INTO .. ON DUPLICATE KEY UPDATE.
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;