@anonymous-piwik-user opened this Issue on March 2nd 2014

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 commented on March 4th 2014 Owner

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-piwik-user commented on March 5th 2014

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.

Powered by GitHub Issue Mirror