How do I make « Transitions » feature work faster on high traffic websites?

Transitions is a very useful Matomo (Piwik) feature, which is also fundamentally different from most of the other Matomo reports. It does not use archiving to pre-process reports but instead it retrieves its information directly from the logs. This means that the Transitions report can take a few seconds to load on websites with more traffic, but on the other hand, Transitions does not require pre-processing and does not affect Archiving performance.

In our tests, Transitions could handle periods with several 100.000 pageviews quite well. Once you get into the millions, it starts to test your patience. But in general, performance depends heavily on the power of your database server and other things like traffic patterns.

Also, the first time you open Transitions on any page might be a fair bit slower than consecutive calls. This is because after the first time, the database caches are hot and the queries can be executed much faster.

If you have lots of traffic, you shouldn’t open Transitions on long periods of time. But that’s not really a problem because the most important thing is to have enough data in order to get statistically representative results. If you achieve this mass with a single day, it should be fine if you only use it for yesterday (or today).
Since the aggregation happens in the database, you can tune things by adding some indexes to the MySQL tables.

You can create the following INDEX to speed up Transitions on page URLs:

ALTER TABLE  `matomo_log_link_visit_action` ADD INDEX `transitions_url` (  `idaction_url` ,  `idsite` ,  `server_time` ), 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 have confirmed that these indexes drastically improve performance for pages that had a fairly low share of the overall traffic. They do add a little extra complexity when inserting new logs but speeding up Transitions also means having the queries it does out of system in a shorter amount of time, which in turn has a positive impact on tracking performance.
You may not need all of these indexes. Instead, you can add them one by one until the performance of Transitions is satisfactory.

Note: Adding these INDEXes will also increase the Performance of Page Overlay reports.