@mattab opened this Issue on April 9th 2012 Member

On a test Piwik, select year period, then visitor log, loading the page is really slow and I suppose could even crash Mysql in some cases.

When looking at year or month or day it should be as fast in all cases, since we select the last N visitors for the last day of the period.

I thought this was fixed already, but it isn't, we should run a clever SQL that is fast just like when looking at one day data.

@BeezyT commented on December 17th 2012 Member

As far as I can see, the problem is as follows:

Take a look at Piwik_Live_API::loadLastVisitorDetailsFromDatabase().
It only does a LIMIT if $filter_limit is set, but there is none.

It loads all visits in the period and the data table widget does pagination.
It's not hard to see why this fails for many visits.

If you do echo $subQuery['sql'];, you get:
SELECT log_visit.* FROM piwik_log_visit AS log_visit WHERE log_visit.idsite = ? AND log_visit.visit_last_action_time >= ? AND log_visit.visit_last_action_time <= ? ORDER BY idsite, visit_last_action_time DESC

It selects everything in the range (which can be millions of rows), sorts it, ships it to PHP and builds a data table. The controller method getVisitorLog does $view->setSortedColumn('idVisit', 'ASC');, which might even sort it again in PHP before applying pagination. Obviously, that won't work for millions of rows in the data table.

What we would have to do is this:

  • Use LIMIT in the SQL query
  • Use calc_found_rows in SQL and add new methods to the data table to generate the pagination without having the actual data
  • Maybe we should avoid sorting on high traffic websites and rely on the ording of the table. Not sure whether this is possible because a GROUP BY is applied to the select query quoted above and AFAIK there is no specified ordering after a GROUP BY
@mattab commented on December 17th 2012 Member

I would propose instead to keep this SQL and call it, for 1 day at a time until there are enough visitors for the display? that would be simpler I think...

@BeezyT commented on December 17th 2012 Member

One day still has an unlimited number of rows. Doing it for one day at a time is an improvement but still not a fix for very high traffic websites.

@BeezyT commented on March 21st 2013 Member

The problem was not as described here. Nevertheless, it should be fixed in [3ac47e89eb7cc6024cffad435e9a6ef2a29488ff]

@mattab commented on March 26th 2013 Member

Great work on this one!

This Issue was closed on May 27th 2013
Powered by GitHub Issue Mirror