What Database / Technology should be used to calculate unique visitors in time scope

manpreet Best Answer 2 years ago

I've got a problem with performance of my reporting database (tables have millions of records, 50+), when I want to calculate distinct on column that indicates a visitor uniqueness, let's say some hashkey.

For example: I have these columns: hashkey, name, surname, visit_datetime, site, gender, etc...

I need to get distinct in time span of 1 year, less than in 5 sec:

SELECT COUNT(DISTINCT hashkey) FROM table WHERE visit_datetime BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' 

This query will be fast for short time ranges, but if it be bigger than one month, than it can takes more than 30s.

Is there a better technology to calculate something like this than relational databases?

I'm wondering what google analytics use to do theirs unique visitors calculating on the fly.

manpreet 2 years ago

For reporting and analytics, the type of thing you're describing, these sorts of statistics tend to be pulled out, aggregated, and stored in a data warehouse or something. They are stored in a fashion meant for performance reasons in lieu of nice relational storage techniques">techniques optimized for OLTP (online transaction processing). This pre-aggregated technique is called OLAP (online analytical processing).