OJS performance problem (on article select reviewers)

[jamil]

Hi people,

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:

Example:

"SELECT DISTINCT

u.user_id,
u.last_name,
ar.review_id,
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"

File: classes/submission/sectionEditor/SectionEditorSubmissionDAO.inic.php
Method: getReviewersForArticle

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?

[asmecher]

Hi all,

See http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=6068 for patches that should cause MySQL to use the indexes created for this purpose.

Regards,
Alec Smecher
Public Knowledge Project Team