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()
and ROW_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 adapter supportsWindowFunctions
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.