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

Wrong Index choosen by MySQL #4740

Open
anonymous-matomo-user opened this issue Feb 23, 2014 · 2 comments
Open

Wrong Index choosen by MySQL #4740

anonymous-matomo-user opened this issue Feb 23, 2014 · 2 comments
Labels
c: Performance For when we could improve the performance / speed of Matomo. Task Indicates an issue is neither a feature nor a bug and it's purely a "technical" change.
Milestone
Backlog (Help wan...

Comments

@anonymous-matomo-user
Copy link

On my MySQL installation
5.5.31-0+wheezy1-log (Debian)

The query regarding the custom var aggregation are using thw wrong index.

Here the Explain
https://gist.github.com/RoyBellingan/9170698

The choosen index is a three column index
idsite
config_id
visit_last_action_time

If a "hint" to use the index_idsite_datetime (two column), a very good improvement can be seen.

https://gist.github.com/anonymous/9170625
@mattab
Copy link
Member

mattab commented Feb 23, 2014

I'm not sure if this is a bug. My guess is that you don't have enough rows in the table. I believe if you had more rows in your table, the query optimizer would pick the right index. Could you try run the same query on 10 or even 100 times more data?

@anonymous-matomo-user
Copy link
Author

Is not a bug, just a performance improvement in reality...

The log_visit is around 1M rows

I've rerunned it now, increasing the time span
log_visit.visit_last_action_time >= '2014-02-10 23:00:00'
AND log_visit.visit_last_action_time <= '2014-02-24 22:59:59'

Explain again suggest to use index_idsite_config_datetime, this time rows count is around 540K.

@anonymous-matomo-user anonymous-matomo-user added this to the 2.x - The Great Piwik 2.x Backlog milestone Jul 8, 2014
@mattab mattab removed the P: normal label Aug 3, 2014
@mattab mattab modified the milestones: Mid term, Long term Oct 11, 2014
@mattab mattab modified the milestones: Long term, Mid term Dec 23, 2015
@mattab mattab modified the milestones: Long term, Mid term Dec 5, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
c: Performance For when we could improve the performance / speed of Matomo. Task Indicates an issue is neither a feature nor a bug and it's purely a "technical" change.
Projects
None yet
Milestone
Backlog (Help wanted)
Development

No branches or pull requests
2 participants
@mattab @anonymous-matomo-user