This has been initially reported in the WP project, I'll copy here the description. It especially affects workplace because we display user courses on every page in the user dropdown.

Hi,
One of our clients is experiencing significant page load times for one particular user. Our investigations have narrowed this down to course/lib.php course_get_recent_courses() method's SQL query. The query in question can take up to 17s to run for one particular user, when logged in as other users it executes in less than 3s.
The query in question for the affected user returns 23 records, so not a huge number. There are no records for the user in the mdl_favourite table, and the slowness appears to emanate from the subquery in the WHERE clause.
I have noticed that the fields being matched against do not have db indexes associated, which may improve performance.
I have run the entire query on my local system against a db clone, and it took over 10 seconds to run for me. This seems like a very unreasonable query time for such a well used method.

To further investigate this issue, ran the subquery directly with two simple modifications. I hardcoded the userid to the affected user's id, and removed the e.courseid = c.id clause. This was to test the speed of this subquery. The query returned 38 records and took 22 seconds.
For your information, the mdl_enrol table contains 2437 records, and mdl_user_enrolments contains 3,105,608 records.
I would appreciate if Moodle HQ could take a look at how best to improve the efficiency of this SQL.