trying to restore a course, it contains a lot of questions with tags, and it takes a long time to restore, and it turns out that there is a bad performance query problem,
the Course retrieval includes SQL Update with bad performance, does not use an index, scans thousands of records, and causes a delay about 40% of the  course  total retrieval time 
​ Moodle Ver:  Moodle 4.2 (Build: 20230424)     

database: MariaDB Galera cluster 10.6

 
​the query from:     "moodle/backup/moodle2/restore_stepslib.php "
    class  restore_move_module_questions_categories 
    function define_execution():
 
5390                 // Update the context id of any tags applied to any questions in these categories.
....
5393                     $sqlupdate = "UPDATE {tag_instance}
5394                                      SET contextid = :newcontext
5395                                    WHERE component = :component
5396                                          AND itemtype = :itemtype
5397                                          AND itemid IN (SELECT DISTINCT bi.newitemid as questionid
5398                                                           FROM {backup_ids_temp} bi
5399                                                           JOIN {question} q ON q.id = bi.newitemid
5400                                                           JOIN {question_versions} qv ON qv.questionid = q.id
5401                                                           JOIN {question_bank_entries} qbe ON qbe.id = qv.questionbankentryid
5402                                                          WHERE bi.backupid = :backupid AND bi.itemname = 'question_created'
5403                                                                AND qbe.questioncategoryid {$categorysql}) ";

 
 
The update Query is executed many times (about 650 times), each time  reads (full table scan ) 231282 records, and it consumes 100% of the CPU, high I/O disk load, and takes about 7 milliseconds each time.  
the query from MariaDB  log" 
 

1. Thread_id: 167  Schema: adminmoodle  QC_hit: No
2. Query_time: 0.691205  Lock_time: 0.000150  Rows_sent: 0  Rows_examined: 231282
3. Rows_affected: 0  Bytes_sent: 52
   SET timestamp=1682537401;
   UPDATE mdl_tag_instance
       SET contextid = '702'
       WHERE component = 'core_question'
       AND itemtype = 'question'     
       AND itemid IN 
       (SELECT DISTINCT bi.newitemid as questionid
       FROM mdl_backup_ids_temp bi
       JOIN mdl_question q ON q.id = bi.newitemid
       JOIN mdl_question_versions qv ON qv.questionid = q.id
       JOIN mdl_question_bank_entries qbe ON qbe.id = qv.questionbankentryid
       WHERE bi.backupid = 'c261c95a74d7bd3e500e42cc14966d5f' AND bi.itemname = 'question_created'
           AND qbe.questioncategoryid = '722');
    
    
    
   I have rewritten  the Update Query as following; so that, following the index, the execution time is greatly reduced, the process becomes fast, and it does not waste time:
    
    $sqlupdate = "UPDATE {tag_instance}  INNER JOIN
                                    (SELECT DISTINCT bi.newitemid as questionid
                                     FROM {backup_ids_temp} bi
                                     JOIN {question} q ON q.id = bi.newitemid
                                     JOIN {question_versions} qv ON qv.questionid = q.id
                                     JOIN {question_bank_entries} qbe ON qbe.id = qv.questionbankentryid
                                     WHERE bi.backupid = :backupid AND bi.itemname = 'question_created'
                                     AND qbe.questioncategoryid {$categorysql})
                                     AS backup_ids_t
                                     ON
                                     itemid = backup_ids_t.questionid
                                  SET contextid = :newcontext
                                  WHERE component = :component
                                        AND itemtype = :itemtype ";
    
    
   and here are the logs after changes from MariaDb :
    
4. Query_time: 0.000342  Lock_time: 0.000087  Rows_sent: 0  Rows_examined: 0
5. Rows_affected: 0  Bytes_sent: 52
   SET timestamp=1682586215;
       UPDATE mdl_tag_instance  INNER JOIN
           (SELECT DISTINCT bi.newitemid as questionid
           FROM mdl_backup_ids_temp bi
           JOIN mdl_question q ON q.id = bi.newitemid
           JOIN mdl_question_versions qv ON qv.questionid = q.id
           JOIN mdl_question_bank_entries qbe ON qbe.id = qv.questionbankentryid
           WHERE bi.backupid = '4aa5a6f405d34e4367f72739e9c0a5d3' AND bi.itemname = 'question_created'
           AND qbe.questioncategoryid = '3244')
       AS backup_ids_t
       ON
       itemid = backup_ids_t.questionid
       SET contextid = '3363'
       WHERE component = 'core_question'
       AND itemtype = 'question';
    
    
   I need to be sure about the modification that I made, in case it does the same result, or it is a wrong modification. In any case, update Query needs to be rewritten better, in order to prevent slow recovery of the course.
    
    
   https://moodle.org/mod/forum/discuss.php?d=446266