Displaying the query used by Piwik on each Piwik page?

Support & Bugs
1

I’m having issues with a large database with lots of sites, where the “all websites” query times out because there’s too many websites. I’d like to see if I can manually execute the query used to call this page directly in MySQL.

Is there a way to turn on a debug mode where the Piwik query is displayed, or perhaps a code edit I can do in one of the files where the query would be displayed and PHP then exits?

2

How many sites do you have in Piwik?

you can enable logging, see Troubleshooting - Analytics Platform - Matomo

This is a know bug: “All Websites dashboard” should load fast even when tracking hundreds of websites! MultiSites · Issue #1077 · matomo-org/matomo · GitHub
the problem is that piwik does too many sql queries (thousands) rather than do less, because the page is using the APIs which are not (yet) optimized, but it’s on the roadmap to fix this ticket: Roadmap - Analytics Platform - Matomo

3

It’s actually in the hundreds, I’d like to keep track of all my sites centrally. Even though the cumulative traffic is only in the hundreds of visits per day, Piwik just fails to load because the queries take too long. Obviously it isn’t designed to work with that many sites I guess, so I was hoping to be able to view stats from MySQL rather than the control panel.

With SQL profiling enabled under ‘all websites’ it didn’t reach the point where any query was displayed. Does anyone have any suggestions for what the correct query would be? I’m thinking something like the following:

SELECT name , piwik_site.idsite, count( * ) , SUM( visit_total_actions ) AS num
FROM piwik_log_visit LEFT JOIN piwik_site ON piwik_log_visit.idsite = piwik_site.idsite
WHERE visit_server_date > '2011-04-01’
GROUP BY idsite
ORDER BY num DESC

Although I’m not sure if visit_total_actions is the right field to sum by in order to get total visits.

4

that query looks good!

5

Another question -

Which fields in the piwik_log_visit table are used to track the ‘Visits’ and ‘Unique Visitors’ stats displayed by Piwik?

6

idvisit and idvisitor

7

Thank you. Is idvisitor something that was recently added? I have 1.0 and that field is not present. I do have visitor_idcookie - would that serve the same purpose?

Here’s my updated query btw - using DISTINCT to get a count of unique visitor cookie id’s:

SELECT name , COUNT( DISTINCT visitor_idcookie ) AS unique_visitors, COUNT( idvisit ) AS total_visits, SUM( visit_total_actions ) AS sum_actions
FROM piwik_log_visit
LEFT JOIN piwik_site ON piwik_log_visit.idsite = piwik_site.idsite
WHERE visit_server_date >= '2011-04-19’
AND visit_server_date <= '2011-04-26’
GROUP BY piwik_log_visit.idsite
ORDER BY unique_visitors DESC

8

Please update to the latest version before asking questions!! Piwik evolves fast…

9

SELECT name , COUNT( DISTINCT idvisitor ) AS unique_visitors, COUNT( idvisit) AS total_visits, SUM( visit_total_actions ) AS sum_actions
FROM piwik_log_visit
LEFT JOIN piwik_site ON piwik_log_visit.idsite = piwik_site.idsite
WHERE visit_first_action_time >= '2016-12-01’
AND visit_first_action_time <= '2016-12-26’
GROUP BY piwik_log_visit.idsite
ORDER BY unique_visitors DESC

this is the correct sql query