Slow search
Since the update for version 3.4.0-1, the site search functionality is very slow and is blocking the database
Steps I took leading up to the issue
Search for anything in the site search field.
What application are you using?
OJS 3.4.0-1
Additional information
I noticed that removing the primary key from submission_search_object_keywords table normalizes the search time.
The query below takes one minute to return with the primary key set in the table and 8 seconds without it.
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)
JOIN journals j ON j.journal_id = s.context_id
LEFT JOIN journal_settings js ON j.journal_id = js.journal_id AND js.setting_name = 'publishingMode'
NATURAL JOIN submission_search_object_keywords o0
NATURAL JOIN submission_search_keyword_list k0
WHERE
(js.setting_value <> '2' OR js.setting_value IS NULL)
AND j.enabled = 1
AND s.status = 3
AND i.published = 1
AND k0.keyword_text = 'sus'
AND i.journal_id = 1
GROUP BY o.submission_id
ORDER BY count DESC
LIMIT 500
I’m not sure why removing the primary key from the table would improve performance… Are you sure you’re not just seeing the effects of MySQL’s query cache on repeat queries for the same data?
I’d suggest running an EXPLAIN on that query (both with and without the primary key in place) to see what it says about the execution plan.
Note that the built-in OJS search engine can only do so much – at a certain point it will not perform well. For larger installations I’d recommend looking into Lucene/SOLR and the Lucene plugin for OJS.
Regards,
Alec Smecher
Public Knowledge Project Team
1|SIMPLE |o |ref |PRIMARY,submission_search_objects_submission_id |submission_search_objects_submission_id |8 |ojs.s.submission_id |4 |Using index |
1|SIMPLE |s |range |PRIMARY,submissions_context_id,submissions_publication_id |submissions_context_id |8 | |4433|Using index condition; Using where; Using join buffer (incremental, BNL join)|
The first formulation is actually a stronger query – all other things being equal, eq_ref joins should perform better than ref / range joins. From the MySQL documentation:
Other than the system and const types, this is the best possible join type.
“All other things being equal” is doing some heavy lifting here, though; I see that the o0 join in the first list has a very high row count. But I would still trust the optimizer to make a good decision here.
What version of MySQL are you using? I wonder if you’re running into #109361 or similar.
Regards,
Alec Smecher
Public Knowledge Project Team