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.
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.
Yeah the time range is huge!
-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
Good find 👍
I've created a PR for that. But I'm not sure if it will improve the performance of the real time map much...
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
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.