In lib/moodlelib.php in the get_complete_user_data() function around line 3777 below the /// Get various settings and preferences comment there's a call to $DB->get_records('course_display', array('userid'=>$user->id)
This function gets called every time a new session starts. On OpenLearn, there are many guest sessions started frequently, so this particular query gets called a lot. There's no index on course_display for just userid, though there is one for course,userid.
This query is appearing rather too often in my slow queries log, so I was wondering whether adding an index just for user would help? I could do this just as an OU customisation, but I guess it must affect some other big sites as well, and since its in Moodle 1.9 and Moodle 2.0 perhaps this is a good time to get the change into core? Happy to code it myself if someone would just give me a thumbs up?
(Note all these code references are for Moodle 2.0 but the same problem exists in Moodle 1.9 which is where I've actually seen the problem on my live system).