Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

When using "IS" segment, use hash to find proper action ID #8363

Closed
quba opened this issue Jul 16, 2015 · 3 comments
Closed

When using "IS" segment, use hash to find proper action ID #8363

quba opened this issue Jul 16, 2015 · 3 comments
Labels
worksforme The issue cannot be reproduced and things work as intended.

Comments

@quba
Copy link
Contributor

quba commented Jul 16, 2015

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
Copy link
Contributor Author

quba commented Jul 16, 2015

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
Copy link
Member

mattab commented Jul 22, 2015

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
Copy link
Contributor Author

quba commented Jul 22, 2015

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

@quba quba closed this as completed Jul 22, 2015
@mattab mattab added the worksforme The issue cannot be reproduced and things work as intended. label Jul 23, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
worksforme The issue cannot be reproduced and things work as intended.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@mattab @quba