I have an installation of OCS running that freezes whenever someone tries to select a reviewer for an abstract. I've pinpointed the problem, and it's a slow query to get the list of users to select the abstract. The users table has 1176 rows, the user_settings table has 819, the roles table has 741 and the review_assignments table 3126. I'm assuming that this query takes so incredible long because of that review_assignments table and how many times that table is called in the query (copied below).
I'm looking for some way to fix this problem since they have completed their abstract submissions and at this point are ready to start reviewing. I've thought of a few things:
- I am wondering if in the upgrade from 22.214.171.124 to 2.3.4 has any improvement to this query that may allow it to execute.
- My only other guess as to what to do would be for them to go in and start deleting reviewers from paper assignments. I'm not even sure if that would be possible, however.
- I'm worried that I will have to go into the database and start deleting these assignments that way. I'd like to avoid that considering I don't want to mess up any relational data.
Does anyone have any insight as to whether or not I am correct in diagnosing the problem? Also, any input on my suggestions above or additional suggestions would be very helpful.
Thanks in advance!
Code: Select all
# Time: 111117 13:09:21
# User@Host: iassid_conf[iassid_conf] @ localhost 
# Query_time: 4499 Lock_time: 300 Rows_sent: 25 Rows_examined: 395310566
AVG(a.quality) AS average_quality,
COUNT(ac.review_id) AS completed,
COUNT(ai.review_id) AS incomplete,
MAX(ac.date_notified) AS latest,
AVG(ac.date_completed-ac.date_notified) AS average
FROM users u
LEFT JOIN review_assignments a ON (a.reviewer_id = u.user_id)
LEFT JOIN review_assignments ac ON (ac.reviewer_id = u.user_id AND ac.date_completed IS NOT NULL)
LEFT JOIN review_assignments ai ON (ai.reviewer_id = u.user_id AND ai.date_completed IS NULL)
LEFT JOIN review_assignments ar ON (ar.reviewer_id = u.user_id AND ar.cancelled = 0 AND ar.paper_id = 505 AND ar.stage = '1')
LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = 'interests')
LEFT JOIN roles r ON (r.user_id = u.user_id)
WHERE u.user_id = r.user_id AND
r.sched_conf_id = '8' AND
r.role_id = 256 GROUP BY u.user_id, u.last_name, ar.review_id ORDER BY u.last_name ASC LIMIT 0,25;