Currently Piwik uses
name column to find action IDs (segment's subquery to filter the data, e.g. when data is filtered using page URL or title). My suggestion is to use hash field to find the action ID. The case here is, that there's an index on this column (
type). Of course it will work only with "IS" segments.
Here is an example query (please note: this one is using
contains segment but there will be only a slightly different subquery for the
Id: 2311356 User: piwik Host: localhost db: piwik Command: Query Time: 2953 State: Copying to tmp table Info: SELECT count(distinct log_inner.idvisitor) AS `1`, count(distinct log_inner.user_id) AS `39` FROM ( SELECT log_visit.idvisitor, log_visit.user_id FROM piwik_log_visit AS log_visit LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit WHERE ( log_visit.visit_last_action_time >= '2014-12-01 08:00:00' AND log_visit.visit_last_action_time <= '2015-01-01 07:59:59' AND log_visit.idsite IN ('1') ) AND ( (log_visit.visitor_returning = '1' OR log_visit.visitor_returning = '2') AND ( log_link_visit_action.idaction_url IN (SELECT idaction FROM piwik_log_action WHERE ( name LIKE CONCAT('%', '/myaccount', '%') AND type = 1 )) ) ) GROUP BY log_visit.idvisit ORDER BY NULL ) AS log_inner
looking at the code it looks like it should not issue the subquery for the "IS" segment, but I may be wrong.
@quba can you paste a query for actual "IS" segment rather than "contains" since this issue is about "Is" segment?
I checked the code as well and the logic looks correct for the "IS" segment. There's only an issue with "Contains" segments.