@siva538 opened this Issue on August 18th 2019 Contributor

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
https://github.com/matomo-org/matomo/issues/14119

Thanks in advance.

Regds,
Sivakumar

@tsteur commented on August 18th 2019 Member

Thanks @siva538

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.

This Issue was closed on August 18th 2019
Powered by GitHub Issue Mirror