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

SQL use BEETWEN date() instead of AND '' #4785

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

SQL use BEETWEN date() instead of AND '' #4785

anonymous-matomo-user opened this issue Mar 2, 2014 · 2 comments
Labels
c: Performance For when we could improve the performance / speed of Matomo. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone
Backlog (Help wan...

Comments

@anonymous-matomo-user
Copy link

I'm using MySQL 5.6

I've noticed most of the query in the archiving phase uses something like

log_link_visit_action.server_time >= 'XXX'
AND log_link_visit_action.server_time <= 'XXX'

So I've made some test and concluded that

server_time BETWEEN date( 'XXX') AND date('XXX')

is a bit faster.

In gist some example
https://gist.github.com/RoyBellingan/9300236
@mattab
Copy link
Member

mattab commented Mar 4, 2014

Thanks for the suggestion. Can you see any article online or official documentation that explains why this would be the case? I would expect mysql to assume these two queries as the same, so it's a bit surprising that one would be faster. Of course it could be the test scenario... (did you test with different dates?)

@anonymous-matomo-user
Copy link
Author

I think is faster not only for the use of BETWEEN but for the use of date who avoid the typecast from datetime to string.

I have a lot of query in another software who got the same "problem", especially if you use something like where date like '2014-02-%' instead of BETWEEN date('2014-02-01') AND ('2014-03-01').

In the upcoming day I'll change the test range and redo the test.

@anonymous-matomo-user anonymous-matomo-user added this to the Future releases milestone Jul 8, 2014
@mattab mattab removed the P: normal label Aug 3, 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. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Milestone
Backlog (Help wanted)
Development

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