I am experimenting with OJS35rc2 and populated my test DB with our historical data. The editor dashboard, which displays the number of submissions in each state (Assigned to me, Active Submissions, etc.) takes a very long time to load.
I pulled the dashboard query from the query log and ran it manually. It reliably takes 36+ seconds to run each time I run it from the command line. I want to determine what is causing the query to take so long. It is quite complex–with some elements having three levels of nested subqueries–and returns the 15 values that are used to populate the dashboard counts. I ran each separately and noted how long they took:
| Query | Time (s) |
|---|---|
| assigned_to_me | 0.027 |
| active | 0.011 |
| needs_editor | 0.013 |
| initial_review | 0.018 |
| needs_reviews | 0.082 |
| awaiting_reviews | 11.479 |
| reviews_submitted | 11.592 |
| reviews_overdue | 11.311 |
| revisions_submitted | 0.012 |
| external_review | 0.012 |
| copyediting | 0.011 |
| production | 0.011 |
| scheduled | 0.01 |
| published | 0.01 |
| declined | 0.01 |
After some investigation it seems that the slowness is coming from the third-level subquery that returns the current review round for each submission:
select rr.submission_id, MAX(rr.round) as current_round
from review_rounds as rr
where rr.stage_id in (select s.stage_id from submissions as s where rr.stage_id = s.stage_id)
group by rr.submission_id;
This query takes 11 seconds to run on our system. Rewriting the query to remove the subquery did not help. According to EXPLAIN, no keys were available. So I altered the two tables to add a key on stage_id as follows:
ALTER TABLE review_rounds ADD KEY `review_rounds_stage_id` (`stage_id`);
ALTER TABLE submissions ADD KEY `submissions_stage_id` (`stage_id`);
This query runs much faster now, and the original dashboard query dropped from 36 seconds to 0.11 seconds. If there are no negative consequences elsewhere, I recommend adding these keys to the two table definitions.
Thanks
Roger