Acording to this FAQ How do I make "Transitions" feature work faster on high traffic websites? FAQ - Analytics Platform - Matomo it is possible to create index in order to generate reports on transitions faster.
The issue is that whenever we try to create the index because of the table size it gets locked… So is there any other inocuous way you can create the index on a larger table?
MariaDB [piwik]> select table_schema, table_name, round(data_length/1024/1024,2) as size_mb from information_schema.tables where table_schema like ‘piwik’ order by size_mb desc limit 10;
±-------------±----------------------------±---------+
| table_schema | table_name | size_mb |
±-------------±----------------------------±---------+
| piwik | piwik_log_link_visit_action | 52703.03 |
| piwik | piwik_log_visit | 24809.03 |
MariaDB [piwik]> explain SELECT count(distinct log_visit.idvisitor) AS 1, count(distinct log_visit.user_id) AS 39 FROM piwik_log_visit AS log_visit WHERE log_visit.visit_last_action_time >= ‘2020-05-05 05:00:00’ AND log_visit.visit_last_action_time <= ‘2020-06-06 04:59:59’ AND log_visit.idsite IN (‘23’);
| possible_keys | key | key_len | ref | rows | Extra
±--------------------------------------------------------------------------±----------------------±--------±------±---------±--------------------
| index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor | index_idsite_datetime | 4 | const | 26193976 | Using index conditio
±--------------------------------------------------------------------------±----------------------±--------±------±---------±--------------------