Update Transitions and furture updates

arno-sim · October 11, 2021, 4:45am

Hi,

I have a short question regarding the following support page:
https://matomo.org/faq/how-to/faq_161/

This page talks about “How do I make “Transitions” feature work faster on high traffic websites?”

There are two queries mentioned on this page. I’m trying to figure out what the impact would be when I run these queries. Would this be a problem for future updates of the Matomo installation?

ps. We are running 4.2.x

Kind regards,

Arno

Lukas · October 11, 2021, 12:17pm

Hi,

You can find a bit more background about this here:

github.com/matomo-org/matomo

Transitions report very slow or fails to load without extra INDEX

opened 08:40AM - 13 Aug 19 UTC

mattab

Major c: Performance

This is a long known issue: Transition report can be very slow to run. Often eve…n times out as it doesn't complete in 30 or 60 seconds. As far as I know, transitions loading slowly is the only part of Matomo that is consistently and predictably slow (on any medium or large site. does not have to be a huge website to experience this slowdown or malfunction). We document the workaround in FAQ: https://matomo.org/faq/how-to/faq_161/ Now that we improved so much of Matomo and everything else should load really fast, it feels unacceptable whenever Transitions takes 30 or 60 or 120 seconds to load. How many hundreds/thousands of people get frustrated when using transitions? Knowing there is a solution, it feels un-necessary. The solution involves adding several INDEX which will make tracking slightly slower. But I reckon the extra INDEXes are worth the benefits of having an always-fast Matomo UI. --> My suggestion is to add the Transitions index into core for everyone while upgrading to Matomo 4.0.0: ``` ALTER TABLE `matomo_log_link_visit_action` ADD INDEX `transitions_url` ( `idaction_url` , `idsite` , `server_time` ); ALTER TABLE `matomo_log_link_visit_action` ADD INDEX `transitions_url_ref` ( `idaction_url_ref` , `idsite` , `server_time` ); ALTER TABLE `matomo_log_visit` ADD INDEX `transitions` ( `visit_entry_idaction_url` , `idsite` , `visit_last_action_time` ); ``` (We would also want to create the index for all cloud customers, as even on our own instance (not large traffic) Transitions takes 60+ seconds to load for a yearly report.)

I don’t know much more than that.

arno-sim · October 12, 2021, 4:54am

Hi Lukas,

Thank you for the link, I have relaid the answer to the responsible team.
However the given page does not answer wither we need to run these indexes again after updating to a Matomo version with database changes and what the impact would be.

Perhaps someone else can collaborate on this?

Kind regards,

Arno