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
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.
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.
core/DataAccess/RankingQuery class aware of the PDO adapter being used (via dependency injection?)
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.