[OJS-3.4.0.8] High SQL Query Load

[shantanusingh]

OJS-3.4.0-8

At times, the application runs noticeably slow. Upon checking the SQL processes on the server during these instances, we observed that the following query was creating a high load, with around 200 queued processes:

select count(*) as aggregate from submissions as s left join publications as po on s.current_publication_id = po.publication_id where s.context_id in (44) and s.status in (3) and (exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘mann’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘mann’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘mann’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘ki’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘ki’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘ki’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘baat’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘baat’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘baat’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘agri’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘agri’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘agri’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘startups’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘startups’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘startups’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘agricultural’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘agricultural’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘agricultural’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘innovations’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘innovations’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘innovations’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘agribusiness’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘agribusiness’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘agribusiness’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘incubation’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘incubation’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘incubation’), ‘%’)) or exists (select * from submission_search_objects as sso inner join submission_search_object_keywords as ssok on sso.object_id = ssok.object_id inner join submission_search_keyword_list as sskl on sskl.keyword_id = ssok.keyword_id where sskl.keyword_text = LOWER(‘india’) and s.submission_id = sso.submission_id) or s.submission_id in (select p.submission_id from publications as p inner join publication_settings as ps on p.publication_id = ps.publication_id where ps.setting_name = ‘title’ and LOWER(ps.setting_value) LIKE CONCAT(‘%’, LOWER(‘india’), ‘%’)) or s.submission_id in (select p.submission_id from publications as p inner join authors as au on au.publication_id = p.publication_id inner join author_settings as aus on aus.author_id = au.author_id where aus.setting_name in (‘givenName’, ‘familyName’, ‘orcid’) and LOWER(aus.setting_value) LIKE CONCAT(‘%’, LOWER(‘india’), ‘%’))) and s.submission_id not in (158627) |

This issue significantly impacts the responsiveness of the application, and we are seeking guidance on how to address it effectively.

[abadan]

It must be the similar articles plugin. Disabling it should solve the problem. If you have a lot of accumulated queries, restarting MySQL after disabling it may allow you to see the result more quickly.

[shantanusingh]

Hi @abadan

Thank you for your reply.

We have disabled the plugin from the journals for now. Let’s observe and monitor the effects to see if there’s any improvement.