@quba opened this Issue on July 16th 2015 Contributor

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 (hash + type). Of course it will work only with "IS" segments.

@quba commented on July 16th 2015 Contributor

Here is an example query (please note: this one is using contains segment but there will be only a slightly different subquery for the is segment).

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
@mattab commented on July 22nd 2015 Owner

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?

@quba commented on July 22nd 2015 Contributor

I checked the code as well and the logic looks correct for the "IS" segment. There's only an issue with "Contains" segments.

This Issue was closed on July 22nd 2015
Powered by GitHub Issue Mirror