Question 21258 in Mysql Question posted in
The official MySQL documentation can be found here.

Is there a better approach in SQL to group actions by day/week/month/year/alltime? – Mysql

I’m thinking my way to do that is a little archaic, not optimized… i don’t need super detailed statistics, lets say i want the number of clicks on a link on a blog post per (actual) day/week/month/year nothing more, don’t want the hour of click, just a number for each corresponding times (day/month/year).

I’ve created this table :

CREATE TABLE `clicks` (
  `file_id` bigint(20) unsigned NOT NULL,
  `filename` varchar(100) NOT NULL,
  `day` int(10) unsigned DEFAULT 0,
  `week` int(10) unsigned DEFAULT 0,
  `month` int(10) unsigned DEFAULT 0,
  `year` int(10) unsigned DEFAULT 0,
  `all` int(10) unsigned DEFAULT 0,
  PRIMARY KEY (`file_id`)
)

And each time there’s a click, i update every column of a row by +1.

UPDATE clicks SET day = day+1, week = week+1 [..] WHERE file_id = $id

And at every end of day/week/month/year there’s a cronjob who will reset the corresponding column for every file. For each day end it will be :

UPDATE clicks SET day = 0 [No WHERE Clause]

And when there’s new click on a file tomorrow, it’ll increment the day column again.

I have a small VPS server, small storage space, small RAM etc.. i just need how many times a file has been clicked this day only (not yesterday), this week (not the week before) etc.. and i’m trying to not have big & slow queries by having a line for each click and having millions of them.

Is my way of doing seems ok, or is there a better approach for what i want ?

Thanks everyone for the help.

Tags:

2

Answers


  1. 0

    You could create a table just storing the clicks, something like this:

    CREATE TABLE clicks (
    file_id INT NOT NULL,
    filename VARCHAR(100) NOT NULL,
    click_time TIMESTAMP NOT NULL
);

    Then you just need to use the group by, to extract the clicks. For Example:

    -- group clicks by day
SELECT DATE(click_time) AS day, COUNT(*) AS clicks
FROM clicks
GROUP BY day;

-- group clicks by week
SELECT YEARWEEK(click_time) AS week, COUNT(*) AS clicks
FROM clicks
GROUP BY week;

    This is quite more efficient and requires less storage

    Login or Signup to reply.
  2. 0

    Build and maintain a "Summary table" by date. Only summarize by DAY, then sum up the counts to get "by week", etc. That also lets you get the tallies for arbitrary ranges of days.

    More on Summary Tables

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search