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.
Did you see any query that can be improved by adding an index on
@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?
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.