Open NeillM opened 3 years ago
Our DBA has noticed a query from this plugin causing full table scans
SELECT * FROM mdl_plagiarism_turnitin_files WHERE statuscode = 'queued' OR statuscode = 'pending' ORDER BY lastmodified LIMIT 0, 100;
Occurrence: MEDIUM
|| id || select_type || table || partitions || type || possible_keys || key || key_len || ref || rows || filtered || Extra || | 1 | SIMPLE | mdl_plagiarism_turnitin_files | NULL | ALL | NULL | NULL | NULL | NULL | 424023 | 19 | Using where; Using filesort |
They have suggested adding a new index on statuscode to the table. In their testing where it was added as UoN_stc the following EXPLAIN was found:
|| id || select_type || table || partitions || type || possible_keys || key || key_len || ref || rows || filtered || Extra || | 1 | SIMPLE | mdl_plagiarism_turnitin_files | NULL | range | UoN_stc | UoN_stc | 43 | NULL | 2 | 100 | Using index condition; Using filesort |
The greatly lowered number of rows examined should mean the query is much more efficient.
Our DBA has noticed a query from this plugin causing full table scans
SELECT * FROM mdl_plagiarism_turnitin_files WHERE statuscode = 'queued' OR statuscode = 'pending' ORDER BY lastmodified LIMIT 0, 100;
Occurrence: MEDIUM
|| id || select_type || table || partitions || type || possible_keys || key || key_len || ref || rows || filtered || Extra || | 1 | SIMPLE | mdl_plagiarism_turnitin_files | NULL | ALL | NULL | NULL | NULL | NULL | 424023 | 19 | Using where; Using filesort |
They have suggested adding a new index on statuscode to the table. In their testing where it was added as UoN_stc the following EXPLAIN was found:
|| id || select_type || table || partitions || type || possible_keys || key || key_len || ref || rows || filtered || Extra || | 1 | SIMPLE | mdl_plagiarism_turnitin_files | NULL | range | UoN_stc | UoN_stc | 43 | NULL | 2 | 100 | Using index condition; Using filesort |
The greatly lowered number of rows examined should mean the query is much more efficient.