Query generated by Live plugin can be slow for large date ranges · Issue #9524 · matomo-org/matomo · GitHub
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.

Sign up for GitHub

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 can be slow for large date ranges #9524

Closed
ghost opened this issue Jan 14, 2016 · 3 comments
Closed

Query generated by Live plugin can be slow for large date ranges #9524

ghost opened this issue Jan 14, 2016 · 3 comments
Assignees
@tsteur
Labels
c: Performance For when we could improve the performance / speed of Matomo.
Milestone
2.16.0

Comments

@ghost
Copy link

ghost commented Jan 14, 2016

The following API call generates a query which will always end up in a PHP timeout.

index.php?module=API&method=Live.getMostRecentVisitorId&idSite=2&format=JSON&token_auth=TOKEN

Generates the following query:

Info: SELECT sub.* FROM (

        SELECT
            log_visit.*
        FROM
            piwik_log_visit AS log_visit
        WHERE
            log_visit.idsite in ('2') 
        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
Copy link
Contributor

quba commented Jan 14, 2016

Even though it's limited to 1 record, it fetches all the rows because it has to be ordered by visit_last_action_time.

Additionally, why is it needed to order it by idsite?

I'd suggest to add there date range and check e.g. last month or allow to specify the date-range.

@tsteur
Copy link
Member

tsteur commented Jan 14, 2016

The idsite is there so it uses the index I suppose (index = idsite, visit_last_action_time, config_id)

@tsteur
Copy link
Member

tsteur commented Jan 14, 2016

Applying by default last month or last week which can be changed optionally sounds good 👍

@mattab mattab added the c: Performance For when we could improve the performance / speed of Matomo. label Jan 15, 2016
@mattab mattab modified the milestones: 2.16.1, 2.16.0 Jan 15, 2016
@tsteur tsteur self-assigned this Jan 17, 2016
tsteur added a commit that referenced this issue Jan 17, 2016
@tsteur
fixes #9524 Query generated by Live plugin is too heavy
cf9424e
@tsteur tsteur mentioned this issue Jan 17, 2016
Merged
@mattab mattab changed the title Query generated by Live plugin is too heavy. Query generated by Live plugin can be slow for large date ranges Jan 18, 2016
@tsteur tsteur mentioned this issue Jan 18, 2016
Merged
@tsteur tsteur mentioned this issue Feb 2, 2016
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tsteur tsteur

Labels
c: Performance For when we could improve the performance / speed of Matomo.
Projects
None yet
Milestone
2.16.0
Development

No branches or pull requests
3 participants
@tsteur @mattab @quba