Hi,
Recently i tried to do an OJS upgrade from OJS 3.2.1-4 to 3.4 but that didn’t go so well so i had to revert back to the old version and import old database from the backup. Since then we started to have problems with OJS being very slow and users complaining about it. It’s a bit strange that we didn’t have this problem before upgrading / reverting back. In DB manager i can see some queries like this one running for too long:
SELECT a.*, ug.show_title, p.locale,
COALESCE(agl.setting_value, agpl.setting_value) AS author_given,
CASE WHEN agl.setting_value <> '' THEN afl.setting_value ELSE afpl.setting_value END AS author_family
FROM authors a
JOIN user_groups ug ON (a.user_group_id = ug.user_group_id)
JOIN publications p ON (p.publication_id = a.publication_id)
JOIN submissions s ON (s.current_publication_id = p.publication_id)
LEFT JOIN author_settings agl ON (a.author_id = agl.author_id AND agl.setting_name = 'givenName' AND agl.locale = 'en_US')
LEFT JOIN author_settings agpl ON (a.author_id = agpl.author_id AND agpl.setting_name = 'givenName' AND agpl.locale = p.locale)
LEFT JOIN author_settings afl ON (a.author_id = afl.author_id AND afl.setting_name = 'familyName' AND afl.locale = 'en_US')
LEFT JOIN author_settings afpl ON (a.author_id = afpl.author_id AND afpl.setting_name = 'familyName' AND afpl.locale = p.locale)
JOIN (
SELECT
MIN(aa.author_id) as author_id,
CONCAT(
ac.setting_value,
' '
,
COALESCE(asg0.setting_value, ''), ' ',
COALESCE(asf0.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa0.setting_value FROM 1 FOR 255), ''), ' '
,
COALESCE(asg1.setting_value, ''), ' ',
COALESCE(asf1.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa1.setting_value FROM 1 FOR 255), ''), ' '
,
COALESCE(asg2.setting_value, ''), ' ',
COALESCE(asf2.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa2.setting_value FROM 1 FOR 255), ''), ' '
,
COALESCE(asg3.setting_value, ''), ' ',
COALESCE(asf3.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa3.setting_value FROM 1 FOR 255), ''), ' '
,
COALESCE(asg4.setting_value, ''), ' ',
COALESCE(asf4.setting_value, ''), ' ',
COALESCE(SUBSTRING(asa4.setting_value FROM 1 FOR 255), ''), ' '
) as names
FROM authors aa
JOIN publications pp ON (pp.publication_id = aa.publication_id)
LEFT JOIN publication_settings ppss ON (ppss.publication_id = pp.publication_id)
JOIN submissions ss ON (ss.submission_id = pp.submission_id AND ss.current_publication_id = pp.publication_id AND ss.status = 3)
JOIN journals j ON (ss.context_id = j.journal_id)
JOIN issues i ON (ppss.setting_name = 'issueId' AND ppss.setting_value = CAST(i.issue_id AS CHAR(20)) AND i.published = 1)
LEFT JOIN author_settings ac ON (ac.author_id = aa.author_id AND ac.setting_name = 'country')
LEFT JOIN author_settings asg0 ON (asg0.author_id = aa.author_id AND asg0.setting_name = 'givenName' AND asg0.locale = 'en_US')
LEFT JOIN author_settings asf0 ON (asf0.author_id = aa.author_id AND asf0.setting_name = 'familyName' AND asf0.locale = 'en_US')
LEFT JOIN author_settings asa0 ON (asa0.author_id = aa.author_id AND asa0.setting_name = 'affiliation' AND asa0.locale = 'en_US')
LEFT JOIN author_settings asg1 ON (asg1.author_id = aa.author_id AND asg1.setting_name = 'givenName' AND asg1.locale = 'cs_CZ')
LEFT JOIN author_settings asf1 ON (asf1.author_id = aa.author_id AND asf1.setting_name = 'familyName' AND asf1.locale = 'cs_CZ')
LEFT JOIN author_settings asa1 ON (asa1.author_id = aa.author_id AND asa1.setting_name = 'affiliation' AND asa1.locale = 'cs_CZ')
LEFT JOIN author_settings asg2 ON (asg2.author_id = aa.author_id AND asg2.setting_name = 'givenName' AND asg2.locale = 'sr_RS@latin')
LEFT JOIN author_settings asf2 ON (asf2.author_id = aa.author_id AND asf2.setting_name = 'familyName' AND asf2.locale = 'sr_RS@latin')
LEFT JOIN author_settings asa2 ON (asa2.author_id = aa.author_id AND asa2.setting_name = 'affiliation' AND asa2.locale = 'sr_RS@latin')
LEFT JOIN author_settings asg3 ON (asg3.author_id = aa.author_id AND asg3.setting_name = 'givenName' AND asg3.locale = 'hr_HR')
LEFT JOIN author_settings asf3 ON (asf3.author_id = aa.author_id AND asf3.setting_name = 'familyName' AND asf3.locale = 'hr_HR')
LEFT JOIN author_settings asa3 ON (asa3.author_id = aa.author_id AND asa3.setting_name = 'affiliation' AND asa3.locale = 'hr_HR')
LEFT JOIN author_settings asg4 ON (asg4.author_id = aa.author_id AND asg4.setting_name = 'givenName' AND asg4.locale = 'it_IT')
LEFT JOIN author_settings asf4 ON (asf4.author_id = aa.author_id AND asf4.setting_name = 'familyName' AND asf4.locale = 'it_IT')
LEFT JOIN author_settings asa4 ON (asa4.author_id = aa.author_id AND asa4.setting_name = 'affiliation' AND asa4.locale = 'it_IT')
WHERE j.enabled = 1
AND (LOWER(asf0.setting_value) LIKE LOWER('%') OR LOWER(asf1.setting_value) LIKE LOWER('%') OR LOWER(asf2.setting_value) LIKE LOWER('%') OR LOWER(asf3.setting_value) LIKE LOWER('%') OR LOWER(asf4.setting_value) LIKE LOWER('%'))
GROUP BY names
) as t1 ON (t1.author_id = a.author_id)
ORDER BY author_family, author_given LIMIT 15300,25
I also want to mention that previously we used MyISAM DB engine, during the upgrade i converted DB to INNODB, but since the upgrade was not successful i had to revert back to the old MyISAM DB again. Now i converted DB to the INNODB but i can see that this query is still taking some time to finish. Does anybody know what we can do and why this started happening after we reverted back to the old database?
EDIT: Now i see here Performance problem in "recommend by author" plugin · Issue #5887 · pkp/pkp-lib · GitHub that there was a problem with “recommend by author” plugin but we don’t use this plugin 
We also don’t use “Recommend Similar Articles” plugin.
Meanwhile i found this: googleSearch report a lot of slowpages linking to author's search · Issue #7183 · pkp/pkp-lib · GitHub. Can this be the cause of the problem? I just did this patch: pkp/pkp-lib#7183 Remove author index · pkp/ojs@db04bf3 · GitHub
Thx..
Regards.