We have a user who reports out on various PIWIK ‘standard’ reports out of the Action option.
The problem is that as the underlying PIWIK database has grown the performance of this report generation (and others in a similar vein) has slowed down and now crashes.
What I don’t currently have access to, is to see the actual queries behind the buttons, but one concern I have, is that as the reporting is limiting to a specific date range (e.g. Last Month), that if the query has a format for finding the date similar to …
WHERE DATE( CONVERT_TZ( server_time, ‘+00:00’, ‘+08:00’ ) ) = ‘2016-05-01’
then MySQL will then not use any supporting index with ‘server_time’ within it and end up doing a table scan as a result as opposed to an index scan or preferably index seek.
as oppose to rewriting the query so any calculations are done on the supplied date range or not at all?
WHERE server_time >= '2016-05-01 00:00:00’
and server_time < ‘2016-06-01 00:00:00’
So can someone enlighten me as to what the underlying queries look like?
Matthieu, thank you for the response. The concern/problem I have, is that I am a DBA not a Developer so the API aspects do not have much meaning to me in that sense.
You flag for SQL to use the field ‘visit_last_action_time’ which is in the table piwik_log_visit on our set up.
But for a ‘standard’ report for Modules Usage per Month (to return all module pages and the corresponding page views by month), what I don’t have is sight of, is the SQL used to get that query, because if the predicate for the visit_last_action_time is constructed in the underlying query in the ‘wrong’ way the two existing indexes with visit_last_action_time in it would in my understanding not be utilised and the query would then tend to have a full table scan as a result. However if the predicate is constructed in a manner the indexing can be used this would be a quicker index scan or maybe even down to an index seek.
So can I kindly request what the underlying SQL is for this report query.
We have a replica of PIWIK database, so the interrogation for the reports can be done ‘away’ from the live data gathering. And ultimately if it turns out the SQL the tool uses is not efficient for the standard reports then this would benefit PIWIK to have better reporting SQL?
Note this is just one of a number of standard reports we use from the tool and in a general sense the same issue is applying across the board particularly as the data volume grows.