Helped by How to get multiple counts with one SQL query? - Stack Overflow I can probably improve the run time performance of https://github.com/hn-88/ad-hoc-moodle-database-queries/blob/main/Files%20by%20Asset%20type
Another possible way to improve performance might be to change the order of the WHERE conditions.
Apparently all queries which use the COUNT() function will lock the table until processing finishes. In this case, for some of the larger numbers like 300,000+ rows, the query takes well over a minute to run, causing the entire site to stop responding. To prevent that from happening, I have not put this query in the Moodle ad-hoc queries list.
To prevent timeouts from other queries, we have to limit the numbers using the parameters, like choosing to query a limited number of days/months, or limited number of users, etc.
No comments:
Post a Comment