[Bug] DBStats: cacheDataByArchiveNameReports could take hours to complete

[mikkeschiren]

What happened?

When running scheduled tasks cacheDataByArchiveNameReports could take hours to complete on a big database:

INFO      [2024-09-10 19:22:14] 260  Scheduler: executing task Piwik\Plugins\DBStats\Tasks.cacheDataByArchiveNameReports...
INFO      [2024-09-11 00:58:40] 260  Scheduler: finished. Time elapsed: 20185.759s
INFO      [2024-09-11 00:58:40] 260  Scheduler: executing task Piwik\Plugins\QueuedTracking\Tasks.optimizeQueueTable...
INFO      [2024-09-11 00:58:40] 260  Scheduler: finished. Time elapsed: 0.002s
INFO      [2024-09-11 00:58:40] 260  Scheduler: executing task Piwik\Plugins\QueuedTracking\Tasks.notifyQueueSize...
INFO      [2024-09-11 00:58:40] 260  Scheduler: finished. Time elapsed: 0.000s

What should happen?

The task should go much quicker. How could we optimise it?

How can this be reproduced?

Can be reproduced on a big Matomo instance

Matomo version

5.1.1

PHP version

8.1

Server operating system

Linux

What browsers are you seeing the problem on?

No response

Computer operating system

No response

Relevant log output

No response

Validations

• Read our Contributing Guidelines.
• Follow our Security Policy.
• Check that there isn't already an issue that reports the same bug to avoid creating duplicates.
• The provided steps to reproduce is a minimal reproducible of the Bug.