Very slow SQL queries when archiving a segment for an unknown action #8102
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
The goal of this issue is to solve a performance issue we experienced on a production Piwik instance measuring thousands of websites. This slow query is reproduced when using a segment that matches to no action eg.
pageUrl@=this-wont-match-anthing
or&segment=pageTitle==Větrnásssssss
with an unknown (not found) url, then the sql query piwik generates is not optimal and mysql spends literally hours running them.Query stats on the prod instance, via mysqldumpslow:
Another more extreme example of how the SQL query segment part can turn out:
The code responsible for this -100 is here: https://github.com/piwik/piwik/blob/2.13.1/core/Tracker/TableLogAction.php#L180-185
Solution: in the Segment SQL creation code, when idaction is -100 (not found), we don't add the SQL OR condition ie
OR log_link_visit_action.idaction_url = '-100'
since it won't match anything anyway. When there is aAND
condition, and idaction was -100 (not found), the logical statement will be false. Didn't look how hard it will be to change...The text was updated successfully, but these errors were encountered: