I am reaching out to request your assistance regarding an issue we are experiencing with our Open Journal Systems (OJS) installation. After updating to OJS 3.4.0.5, we have identified a SQL query that is being executed multiple times and is significantly impacting the performance of our database server. Below is the problematic query:
Summary
SELECT ‘s’.* FROM ‘submissions’ AS ‘s’
LEFT JOIN ‘publications’ AS ‘po’ ON ‘s’.‘current_publication_id’ = ‘po’.‘publication_id’
WHERE ‘s’.‘context_id’ IN (3)
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(‘elecciones’) 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(‘elecciones’), ‘%’)
)
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(‘elecciones’), ‘%’)
)
– Similar queries for other keywords
)
ORDER BY (
SELECT COUNT(DISTINCT sskl.keyword_id)
FROM ‘submission_search_objects’ AS ‘sso’
INNER JOIN ‘submission_search_object_keywords’ AS ‘ssok’ ON ‘ssok’.‘object_id’ = ‘sso’.‘object_id’
INNER JOIN ‘submission_search_keyword_list’ AS ‘sskl’ ON ‘sskl’.‘keyword_id’ = ‘ssok’.‘keyword_id’
WHERE (
‘sskl’.‘keyword_text’ = LOWER(‘elecciones’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘el’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘salvador’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘1988’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘política’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘gobierno’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘derechos’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘humanos’)
) AND ‘s’.‘submission_id’ = ‘sso’.‘submission_id’
) DESC,
(
SELECT COUNT(0)
FROM ‘submission_search_objects’ AS ‘sso’
INNER JOIN ‘submission_search_object_keywords’ AS ‘ssok’ ON ‘ssok’.‘object_id’ = ‘sso’.‘object_id’
INNER JOIN ‘submission_search_keyword_list’ AS ‘sskl’ ON ‘sskl’.‘keyword_id’ = ‘ssok’.‘keyword_id’
WHERE (
‘sskl’.‘keyword_text’ = LOWER(‘elecciones’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘el’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘salvador’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘1988’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘política’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘gobierno’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘derechos’)
OR ‘sskl’.‘keyword_text’ = LOWER(‘humanos’)
) AND ‘s’.‘submission_id’ = ‘sso’.‘submission_id’
) DESC
LIMIT 10 OFFSET 0
LIMIT 1;
The above query involves multiple joins and subqueries and is executed repeatedly, causing a significant load on our database. We have not been able to identify what internal process in OJS is triggering this query.
We would like to know:
- Is there a way to identify which part of OJS is generating this query?
- Are there any configurations in OJS that we could adjust to optimize this query or reduce its frequency?
- Do you recommend any specific strategies to improve the performance of this query in the context of OJS?
We have enabled the slow query log in MySQL and have observed that this query is one of the most frequent and costly in terms of execution time.
Any guidance or suggestions you can offer would be greatly appreciated.
Thank you in advance for your attention and support.