@mattab opened this Issue on April 12th 2018 Member

On a site with 100,000 pageviews and 1M events per day, the Real time map is very slow to load. The slow SQL query is:

SELECT sub.* FROM ( 
 SELECT log_visit.* 
 FROM log_visit AS log_visit 
 WHERE log_visit.idsite in ('28') 
      AND log_visit.visit_last_action_time > '1969-12-31 23:59:59' AND log_visit.visit_last_action_time >= '2018-03-14 08:05:48' 
 ORDER BY idsite DESC, visit_last_action_time DESC 
 LIMIT 0, 100 ) 
AS sub 
GROUP BY sub.idvisit 
ORDER BY sub.visit_last_action_time DESC LIMIT 100

It would be great if we could find a way to make the Real time map load faster.

The exact same query is triggered from the Visitor Log for the same day/website.

@fdellwing commented on April 12th 2018 Contributor

Can anybody explain this?

AND log_visit.visit_last_action_time > '1969-12-31 23:59:59' AND log_visit.visit_last_action_time >= '2018-03-14 08:05:48'

My understanding is, that the query will first check for every visit that happened after time 0 and than afterwards for everything from current time. So it will check every row 2 times, but only rows with second check true will be included.

@tsteur commented on April 12th 2018 Member

Yeah the time range is huge!

@sgiehl commented on April 20th 2018 Member

The reason for that is imho located in https://github.com/matomo-org/matomo/blob/3.x-dev/plugins/UserCountryMap/javascripts/realtime-map.js#L151
the -1 given as minTimestamp is interpreted as timestamp (as it's not empty), which results in 1969-12-31 23:59:59. Setting it to 0 instead of -1 should remove the additional where

@tsteur commented on April 21st 2018 Member

Good find 👍

@sgiehl commented on April 21st 2018 Member

I've created a PR for that. But I'm not sure if it will improve the performance of the real time map much...

@mattab commented on April 23rd 2018 Member

Will mark as closed for now as we have at least fixed the obvious issue. I would re-open another issue then in case the performance problems persist

@tsteur commented on April 23rd 2018 Member

I'll reopen it right now as it is not properly fixed. I could create a new issue instead but there is no point in it.

@mattab commented on June 28th 2018 Member

@Findus23 you re-opened the issue, did you experience it again or have some more info maybe?

@Findus23 commented on June 29th 2018 Member

@mattab Thanks for the info.
I have no other explaination than having misclicked the button as I have nothing to say to this issue.

This Issue was closed on June 29th 2018
Powered by GitHub Issue Mirror