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