SQL query to count the total number of pageviews for each Page URL

Support & Bugs
1

in https://matomo.org/faq/how-to/how-do-i-write-sql-queries-to-select-visitors-list-of-pageviews-searches-events-in-the-matomo-database/

SQL query to count the total number of pageviews for each Page URL:

 SELECT name as page_url, COUNT(*) as hits
    FROM matomo_log_visit
    LEFT JOIN matomo_log_link_visit_action ON matomo_log_visit.idvisit = matomo_log_link_visit_action.idvisit
    LEFT JOIN matomo_log_action ON matomo_log_action.idaction = matomo_log_link_visit_action.idaction_url
    WHERE matomo_log_visit.idsite = X
    AND type = 1
    AND matomo_log_link_visit_action.server_time >= '2021-06-01'
    AND matomo_log_link_visit_action.server_time < '2021-06-30'
    GROUP BY page_url
    ORDER BY hits DESC;

I’ve run above query for about 25 days. it’s been slow and took 30+ seconds every time.

after some further investigation, I tried to change
WHERE matomo_log_visit.idsite = X
to
WHERE matomo_log_link_visit_action.idsite = X
and the query time drops to 0.xx second(less than 1 second), can matomo confirm if this change is ok ?

it looks an existing index has done the trick:

ALTER TABLE `matomo`.`matomo_log_link_visit_action` 
      DROP INDEX `index_idsite_servertime`, 
       ADD INDEX `index_idsite_servertime` (`idsite`, `server_time`) USING BTREE;