@pleaded opened this Issue on July 12th 2018

Hi everyone,

Could you please let me know the reasons why "_visit_first_actiontime" is not a part of any index in _logvisit table? This field, basically, represents the timestamp of a visit itself, e.g. one the most straightforward information of the tracking in common.

Is it safe to create the index on that field manually in order to speedup analytical queries?

Thank you a lot in advance.

@sgiehl commented on July 16th 2018 Member

Did you see any query that can be improved by adding an index on visit_first_action_time?

@pleaded commented on July 17th 2018

@sgiehl thank you for the response!
No, I'm not talking about matomo code itself.

My concern is about the usage of the database outside of piwik. It looks very obvious for me that the table that stores information about "visits" should allow us to fetch those visits when they were fired. Please let me know if my assumption is not reasonable?
Is it a bad practice to require that information from matomo in that way by fetching data from piwik_visit_log?

@tsteur commented on July 17th 2018 Member

It is reasonable to use it for custom analysis. In Matomo, it was at some point decided to use the last action timestamp which is why there is an index for it and pretty much all queries use that index. In some cases, the first action time makes more sense for sure. For example the "visit by hour" report definitely should be using the first_action_time column. We're just not planning on adding another index which wouldn't be used by any of our reports as it would make the tracking slower and require more storage.

If you're doing some raw data analysis or writing some custom plugins feel free to add an index to your DB.

This Issue was closed on May 24th 2020
Powered by GitHub Issue Mirror