When we have a segment like
segment=pageUrlemail@example.com/en, Piwik executes a subquery to filter the data set. This query uses condition
where name like %subdomain.domain.com/en%.
Everything works OK but the case is that Piwik executes the same query for each archiver query. In ideal case Piwik would query the action IDs only once and then cache it.
Performance use case
I think that this improvement would greatly improve Piwik performance, mostly for users who are using segments processed in realtime.
Thanks @quba for suggestion!
Ideally we would have exact data to backup our feeling it would speed things up, ie. what is % time spent in sub-query VS the whole archiver query. If we cache the result of subquery, we know we'd save this % in each subsequent archiver query. Waiting for more data to know whether it's worth it before we schedule the issue
see Cache segment subqueries using LIKE constraint #8850