This was originally noticed by eduardcercos on moodle.org. When we tested MDLSITE-6104, tsala filled a data export request for her account, which got stuck in the processing state. Eduard reported there is one SQL query taking extremely long to process. We were able to trace it down to the query in the mod_forum\privacy\provider::export_all_posts() method.

In the isolated staging environment on the DB server that had nothing else to do, executing the query to export Helen's data took 1 hour and 48 minutes. On the production environment, it seemed to never be able to finish and it was suspected to cause significant drop in the perceived performance of the whole site.

I assume that the problem here lies in the usage of LEFT JOINs combined with the OR conditions that cause the SQL server to scan through too many combinations to seek for NOT NULL values.

After the query was reimplemented to use three separate queries, optimised thanks to INNER JOINs and UNIONed into the final data set, the same data were obtained in less than a few seconds. And I added an index over the privatereplyto field (because it is used as a search criteria here), the data were received almost instantly in 0.2 seconds.

While fixing this, I was also looking at other queries in the class to see if there is a potential for improvements. I found a candidate in the export_discussion_data() but there I did not see a straightforward way to rewrite it. And because we did not have any actual problem with that query (took a few seconds to finish in this test case), I decided to not fix what ain't broken and I am leaving it be.