In #8861 we introduced a mechanism to cache some of the subqueries done in Piwik archiving process. These speedups could technically bring up to 50% speed improvement in some particular cases. It is un-expected that the cache should make the execution time slower, but it could occur in some cases.
[General] ; whether to enable subquery cache for Custom Segment archiving queries enable_segments_subquery_cache = 1 ; Any segment subquery that matches more than segments_subquery_cache_limit IDs will not be cached, ; and the original subquery executed instead. segments_subquery_cache_limit = 100000 ; TTL: Time to live for cache files, in seconds. Default to 60 minutes segments_subquery_cache_ttl = 3600
This is a a persistent issue in production, Matomo Archiving is often slow or often fails for some segments that include "Page URL" or "Action" criteria. Could we enable this setting by default?
Problem is that for some cases, enabling this setting actually makes archiving slower overall. We end up this slow SQL query eg.
SELECT idaction FROM log_action WHERE ( name LIKE CONCAT('example.com/page', '%') AND type = 1 ) which take 30min or more.
So we cannot just enable this feature as it may not always help and rather make things worse. Requires more investigation.