One user's install appears to become bloated due the INNER JOIN in Piwik\DataAccess\Model::getInvalidatedArchiveIdsSafeToDelete(). The user has more than one hundred thousand invalid archives. On this install, the query never finished (I let it run for an hour before stopping), and for some reason, the scheduled task that purges invalidated archives returns immediately with an empty result set.
The query I've replaced it w/ takes ~.7s to run when there are ~135,000 invalid archives in a single table.
It's possible this may fix #7181, after fixing this in the user's Piwik, the scheduled task started working. Currently waiting for verification.
Very nice! I learnt something new about the Power of GROUP_CONCAT.
Note: this code is covered by System test: ArchivePurgerTest
Great find @diosmosis :+1: