Hello @jnugent
Thanks for your tip. Through the “debug = ON” option for database queries I discovered who is to blame for this delay in loading the page.
There are 2 SQL clauses that together consume the page response time.
SQL 1
mysql> SELECT DISTINCT u.user_id, u.last_name, ar.review_id, (SELECT AVG(ra.quality) FROM review_assignments ra WHERE ra.reviewer_id = u.user_id) AS average_quality, (SELECT COUNT(ac.review_id) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS completed, (SELECT COUNT(ac.review_id) FROM review_assignments ac, articles a WHERE ac.reviewer_id = u.user_id AND ac.submission_id = a.article_id AND ac.date_notified IS NOT NULL AND ac.date_completed IS NULL AND ac.cancelled = 0 AND ac.declined = 0 AND a.status <> 1) AS incomplete, (SELECT MAX(ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) AS latest, (SELECT AVG(ac.date_completed-ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 2483 AND ar.round = 1) LEFT JOIN roles r ON (r.user_id = u.user_id) LEFT JOIN articles a ON (ra.submission_id = a.article_id) WHERE u.user_id = r.user_id AND r.journal_id = 2 AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id ;
SQL 2
mysql> SELECT DISTINCT u.user_id, u.last_name, ar.review_id, (SELECT AVG(ra.quality) FROM review_assignments ra WHERE ra.reviewer_id = u.user_id) AS average_quality, (SELECT COUNT(ac.review_id) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS completed, (SELECT COUNT(ac.review_id) FROM review_assignments ac, articles a WHERE ac.reviewer_id = u.user_id AND ac.submission_id = a.article_id AND ac.date_notified IS NOT NULL AND ac.date_completed IS NULL AND ac.cancelled = 0 AND ac.declined = 0 AND a.status <> 1) AS incomplete, (SELECT MAX(ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL) AS latest, (SELECT AVG(ac.date_completed-ac.date_notified) FROM review_assignments ac WHERE ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL AND ac.cancelled = 0) AS average FROM users u LEFT JOIN review_assignments ra ON (ra.reviewer_id = u.user_id) LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.submission_id = 2483 AND ar.round = 1) LEFT JOIN roles r ON (r.user_id = u.user_id) LEFT JOIN articles a ON (ra.submission_id = a.article_id) WHERE u.user_id = r.user_id AND r.journal_id = 2 AND r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id ORDER BY u.last_name ASC LIMIT 0,25;
Running the SQL together with the explain command returns the same information for the two clauses because they are the same, differing only in the ORDER BY statement:
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+-------------------------+------+----------------------------------------------+
| 1 | PRIMARY | r | ref | roles_pkey,roles_journal_id,roles_user_id,roles_role_id | roles_role_id | 8 | const | 3394 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 8 | RVQOJS.r.user_id | 1 | |
| 1 | PRIMARY | ra | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | RVQOJS.r.user_id | 2 | |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | RVQOJS.ra.submission_id | 1 | Using index |
| 1 | PRIMARY | ar | ref | review_assignments_submission_id,review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | RVQOJS.u.user_id | 2 | |
| 6 | DEPENDENT SUBQUERY | ac | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 2 | Using where |
| 5 | DEPENDENT SUBQUERY | ac | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 2 | Using where |
| 4 | DEPENDENT SUBQUERY | ac | ref | review_assignments_submission_id,review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 2 | Using where |
| 4 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 8 | RVQOJS.ac.submission_id | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | ac | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | ra | ref | review_assignments_reviewer_id | review_assignments_reviewer_id | 8 | func | 2 | |
+----+--------------------+-------+--------+-----------------------------------------------------------------+--------------------------------+---------+-------------------------+------+----------------------------------------------+
Why do these clauses take so long to respond?
Can you see any problems?
How can I solve this? 


Regards,
Renato