I migrated ojs 2.2.1 to 2.3.3-1 everything is alright, but we are having a problem on select the reviewers on article page link, this request send to the database the following query:
AVG(ra.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 ra ON (ra.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.submission_id = 41220 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)
LEFT JOIN controlled_vocabs cv ON (cv.assoc_id = u.user_id AND cv.symbolic = 'interest')
LEFT JOIN controlled_vocab_entries cve ON (cve.controlled_vocab_id = cv.controlled_vocab_id)
LEFT JOIN controlled_vocab_entry_settings cves ON (cves.controlled_vocab_entry_id = cve.controlled_vocab_entry_id)
WHERE u.user_id = r.user_id AND
r.journal_id = '6' AND
r.role_id = 4096 GROUP BY u.user_id, u.last_name, ar.review_id"
But this query takes more than one hour to run, in last version 2.2.1 with the same database we didn't have this problem, this query is complex and we are trying to optimize it.
Everybody are having this same problem and if pass to the same problem? Or and idea to optimize this query?