Hello All -
Wanted to bring to your notice an issue with the viewing of Segmented Visitor Log, of Event Categories/Actions/Names.
The query trying to retrieve Segmented visitor log (top 50 records), for a given Event Action is taking close to 4 minutes.
Here is the query for reference:
`SELECT sub.* FROM (
SELECT log_inner.* FROM ( SELECT log_visit.* FROM matomo_log_visit AS log_visit LEFT JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.idsite IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33') /* other sizes are not mentioned considering the length */ AND log_visit.visit_last_action_time >= '2019-07-01 04:00:00' AND log_visit.visit_last_action_time <= '2019-08-01 04:00:00' ) AND ( log_link_visit_action.idaction_event_action = '489906' ) ORDER BY visit_last_action_time DESC LIMIT 0, 510 ) AS log_inner ORDER BY visit_last_action_time DESC ) AS sub GROUP BY sub.idvisit ORDER BY sub.visit_last_action_time DESC LIMIT 51;`
After due investigation, it was identified that there a missing on log_link_visit_action table, that is causing the entire MYSQL box crazy.
Here is the index for reference:
CREATE INDEX index_idaction_event_action ON matomoprod.matomo_log_link_visit_action (idaction_event_action ASC) ;
This actually did the trick. Though we are trying to retrieve the data for a month range, the query started taking < 1-2 sec, compared to 4 mins earlier.
Also as a general thumb rule we add foreign key indexes on the transaction tables, for faster retrieving of the data.
Till we have a broader solution to address this as mentioned in the below issue (i.e. splitting log_link_visit_action table), indexes should help address the problem
Thanks in advance.
We've actually made some more performance improvements to visitor log as part of 3.12 (maybe even 3.11 not sure).
Adding an index can always help with specific queries but we won't be adding any new indexes as everyone has different segments and we can't add heaps of indexes.
Be good to check out 3.12 once it's out. It might improve the performance there as well.