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

Query generated by Live plugin is too heavy #9537

Merged
merged 1 commit into from Jan 17, 2016
Merged

Query generated by Live plugin is too heavy #9537

merged 1 commit into from Jan 17, 2016

Conversation

tsteur
Copy link
Member

@tsteur tsteur commented Jan 17, 2016

fixes #9524

Makes the query faster by first looking max 7 days back. If there was no visitor within 7 days (which should be rather rare), then we issue another query and look back all logs.

Query basically now looks like

            SELECT sub.* FROM (
            SELECT
                log_visit.*
            FROM
                piwik_log_visit AS log_visit
            WHERE
                log_visit.idsite in (1) 
                AND log_visit.visit_last_action_time > '2016-01-10 21:21:32'
            ORDER BY
                idsite, visit_last_action_time DESC LIMIT 1
            ) AS sub
            GROUP BY sub.idvisit
            ORDER BY sub.visit_last_action_time DESC

@quba can you check if it is faster?

@tsteur tsteur added not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. Needs Review PRs that need a code review labels Jan 17, 2016
@tsteur tsteur added this to the 2.16.0 milestone Jan 17, 2016
@sgiehl
Copy link
Member

sgiehl commented Jan 17, 2016

Code LGTM.

tsteur added a commit that referenced this pull request Jan 17, 2016
@tsteur
Merge pull request #9537 from piwik/9524
b545bb4 
Query generated by Live plugin is too heavy
@tsteur tsteur merged commit b545bb4 into master Jan 17, 2016
@tsteur tsteur deleted the 9524 branch January 17, 2016 22:52
@quba
Copy link
Contributor

quba commented Jan 18, 2016

Cool, it should work in most of the cases. I'm not sure about scanning all logs. Would be nice to fallback to some other predefined period, e.g. one year or 2 years, so that it still uses MySQL index. Imagine there are 2 sites - one with only a few visits monthly (idsite=1) and second one which is really big (300M actions/monthly). In this case, this query would be really heavy in case there have been no visits for idsite=1 since last week.

@tsteur
Copy link
Member Author

tsteur commented Jan 18, 2016

I can issue another PR to fall back to one year, and then all visits.

@tsteur tsteur mentioned this pull request Jan 18, 2016
Merged
@tsteur
Copy link
Member Author

tsteur commented Jan 18, 2016

See #9547

@quba
Copy link
Contributor

quba commented Jan 18, 2016

👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
Needs Review PRs that need a code review not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Milestone
2.16.0
Development

Successfully merging this pull request may close these issues.

None yet
3 participants
@tsteur @sgiehl @quba