Best way to aggregate logging data

debugcn Published at Dev

s1h4d0w

So I have a database that contains logs of people clicking a link. What I store is id, country, referrer-domain, clickedat. That last column is a timestamp.

At the moment there are not a lot of rows, but if this takes off it might be tens to hundreds of thousands of rows. What is the best way to query the database for things like this:

Times viewed per day for the past month
Top 20 countries that used your link
Top 20 websites referring your link

Using a COUNT(*) would eventually be way too slow. I've seen techniques where you add another query to every update, insert, delete that happens to save in a special aggregation table. But I'm not sure that would work, as I'd like to have users be able to select two specific dates for example. Or I'd have to aggregate by day.

JeffUK

If you add an indexed Date, column, so you're not doing date/time calculations on the fly, you should just be able to query it using normal aggregations. It would take a long time before that would be 'way too slow' with properly formatted queries.

If it takes off, look into de-normalising the data as you describe, but don't optimise it prematurely!

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-26

Comments

0 comments

From Dev

Related Related

Article

Best way to aggregate logging data

Best way to aggregate logging data

What is the best way to index aggregate data on ElasticSearch

Flutter - Best way to aggregate data from child widgets in an IndexedStack

Best way to aggregate a simple dict in Python

Best way to aggregate a simple dict in Python

Best way to concatenate column values in aggregate

Best way of logging a user in C++

Better way to aggregate timestamped data?

Best way to sort data

Best way to encode data

Best way to transform data

Best way to persist data?

How do I aggregate data from the morgan logging library?

What is the best way to aggregate Streams into one DISTINCT with Java 8

Best way to handle disposable data

best way to store data in session

Best way to build this data structure

Best way to model data in Firebase

Best way to import data in Swift?

Best way of representing binary data

Best way to access data in react

Best way to save data in android

Best way of representing binary data

Best way for store data into client

Best way to deal with xyz data

Best way to learn data structures

Best way to import data in Swift?

What is the best way to add logging for creation of a Django Model instance?

Best way to handle file operation for Logging last 1000 Events

Manipulate aggregate data in usable way in mongodb