I have a website with millions of URLs. Each time you click on a URL, the database row corresponding to that URL indicating the timestamp of that click is updated. I would like, using additional columns, but without the need to insert separate rows for each click, estimate the number of clicks per hour received by this URL. Some ideas include storing multiple timestamps that are aligned with the last seconds, minutes, 15 minute and hour intervals (but this idea is fuzzy for me how it actually gets what we want), or more unpleasant a solution to serializing the "log" time deltas in some serialized string.
While the naive approach suggests measuring the time between the current click and the last one to determine the speed, this will only give a useful estimate if the link is clicked at a very stable speed. In fact, the link can get a flurry of clicks in one minute and nothing at all for another 20.
the reason why I don’t want to log every click clearly is because the database is not burdened with thousands of extra INSERT instructions per hour (and corresponding DELETE data for more than an hour), or, alternatively, I don’t need to start an additional storage system (tokyo tyrant, grepping apache logs, etc.) to register these clicks.
source
share