Slow query execution [OJS version 3.3.0-13]

Software Support
1

Hi all,

version OJS: OJS version 3.3.0-13
database type: InnoDB

Need help optimizing database performance. At the moment, the log database is about 600 MB in size and has over 1,100,000 rows. The problem is in the following queries:
SELECT COUNT(*) AS ROW_COUNT FROM - average processing speed is 90 seconds.
[Description of the request being executed (server information)] Run time: 00:01:35
**Rows Scanned: 1170065 Rows Received: 1 Blocked: 00:00:00 **

  1. SELECT COUNT() AS ROW_COUNT FROM (SELECT s. FROM submissions AS s LEFT JOIN publications AS p ON p.submission_id = s.submission_id LEFT JOIN publication_settings AS ps ON p.publication_id = ps.publication_id LEFT JOIN authors AS au ON p.publication_id = au.publication_id LEFT JOIN author_settings AS aus ON aus.author_id = au.author_id WHERE s.context_id = 1 AND s.status IN (4, 3, 5) AND ((ps.setting_name = ‘title’ AND LOWER(ps.setting_value) LIKE ‘%с%’) OR (aus.setting_name = ‘givenName’ AND LOWER(aus.setting_value) LIKE ‘%с%’) OR (aus.setting_name = ‘familyName’ AND LOWER(aus.setting_value) LIKE ‘%с%’) OR (aus.setting_name = ‘orcid’ AND LOWER(aus.setting_value) = ‘с’)) GROUP BY s.submission_id ORDER BY s.date_submitted DESC) AS count_subquery

[Description of the request being executed (server information)] Run time: 00:01:33
Rows Scanned: 1163955 Rows Received: 23 Blocked: 00:00:00
2)SELECT s.submission_id FROM submissions AS s LEFT JOIN publications AS p ON p.submission_id = s.submission_id LEFT JOIN publication_settings AS ps ON p.publication_id = ps.publication_id LEFT JOIN authors AS au ON p.publication_id = au.publication_id LEFT JOIN author_settings AS aus ON aus.author_id = au.author_id WHERE s.context_id = 1 AND s.status IN (4, 3, 5) AND ((ps.setting_name = ‘title’ AND LOWER(ps.setting_value) LIKE ‘%онешко%’) OR (aus.setting_name = ‘givenName’ AND LOWER(aus.setting_value) LIKE ‘%онешко%’) OR (aus.setting_name = ‘familyName’ AND LOWER(aus.setting_value) LIKE ‘%онешко%’) OR (aus.setting_name = ‘orcid’ AND LOWER(aus.setting_value) = ‘онешко’)) GROUP BY s.submission_id ORDER BY s.date_submitted DESC

3)[Description of the request being executed (server information)] Run time: 00:00:41
Rows Scanned: 4114945 Rows Received: 500 Blocked: 00:00:00
SELECT
o.submission_id,
MAX(s.context_id) AS journal_id,
MAX(i.date_published) AS i_pub,
MAX(p.date_published) AS s_pub,
COUNT(*) AS COUNT
FROM
submissions s
JOIN publications p ON (p.publication_id = s.current_publication_id)
JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’ AND ps.locale=‘’)
JOIN issues i ON (CAST(i.issue_id AS CHAR(20)) = ps.setting_value AND i.journal_id = s.context_id)
JOIN submission_search_objects o ON (s.submission_id = o.submission_id)
NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0
WHERE
s.status = 3 AND
i.published = 1 AND k0.keyword_text = ‘управління’ AND i.journal_id = 1
GROUP BY o.submission_id
ORDER BY COUNT DESC
LIMIT 500

How can these queries be optimized, perhaps indexes are needed for some tables?

Best regards,

2

Hi @Pirate

The main problem here are the %% wild card searches in text fields. Because OJS is written to be compatible with more than just MySQL, we cannot leverage for example the MySQL fulltext search indexes that may help here. In some cases an external search engine may be used, and there are plugins for such things e.g. the Lucene and SOLR plugin that can provide faster search capabilities when document metadata is stored there instead.

If you don’t want to use an external search engine you may also consider a local code modification to make use of MySQL’s fulltext searching. If you’re curious about how those queries are being optimized on your server you can preface each of those with an EXPLAIN statement which will tell you what indexes are being used.

Best
Jason

1 Like
3

Thank you very much for your advice. I use the EXPLAIN command to understand how to optimize queries without resorting to external plugins.

Best regards