Support an option for ranking queries to use window functions #19646
Labels
Enhancement
For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone
Currently ranking queries in Matomo are generated using a iterative counter method, this works well in MySQL but isn't well supported for other database types and can suffer from performance issues.
MySQL 8.0+ introduces new window functions which offer standardization and improved performance for ranking queries by allowing query planner optimizations. Specifically the
RANK()
andROW_NUMBER()
functions can be used to replace counters.Windows functions are only supported for MySQL in version 8.0+, so old style counter queries will still need to be generated for MySQL 5.5 - 5.7.
Suggested approach
Add a PDO adapter interface boolean option
supportsWindowFunctions
, the standard Matomo MySQL PDO adapter should return false. Future PDO adapters for alternative databases that support windows functions can return true.Make the
core/DataAccess/RankingQuery
class aware of the PDO adapter being used (via dependency injection?)Have the
RankingQuery
class check the PDO adaptersupportsWindowFunctions
option and either generate a counter style ranking query using existing code or generate a query using the new window functions.Code using the
RankingQuery
class should not need to be aware of whether a counter or window function ranking query is being generated (note: the goals by page sub-query might be an exception to this).There should be no change at all to ranking queries generated when using the standard Matomo MySQL PDO adapter.
A MySQL8 PDO adapter descendant of the standard MySQL PDO adapter could be created to override this option and allow window function usage on MySQL 8.
The text was updated successfully, but these errors were encountered: