I have a large site where this query is using an entire (also large) CPU for 6 hours each night:

SELECT COUNT  FROM mdl_logstore_standard_log WHERE courseid = $1 and timecreated > $2 and crud <> ? and target <> ?

I've tracked it down to backup_cron_automated_helper::course_is_modified use of get_events_select_count in the various sql log readers used by Moodle.

Moodle doesn't need to be counting modifications to figure out if a course has been modified; we just need to find a single modification.

Instead of calling $DB->count_records_select('logstore_standard_log', $selectwhere, $params) we can call $DB->record_exists_select('logstore_standard_log', $selectwhere, $params)

Whilst this won't really reduce the upper bounds of the query cost it will substantially reduce the minimum, which should result in a significant reduction in average cost of this query.