Slow site search results

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

Hi @liviagouvea,

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

Hi @asmecher I don’t think is cache. See below.

This is the explain result with key


id|select_type|table|type  |possible_keys                                                                                  |key                                         |key_len|ref                                     |rows |Extra                                          |
--+-----------+-----+------+-----------------------------------------------------------------------------------------------+--------------------------------------------+-------+----------------------------------------+-----+-----------------------------------------------+
 1|SIMPLE     |j    |const |PRIMARY                                                                                        |PRIMARY                                     |8      |const                                   |1    |Using temporary; Using filesort                |
 1|SIMPLE     |k0   |const |PRIMARY,submission_search_keyword_text                                                         |submission_search_keyword_text              |182    |const                                   |1    |Using index                                    |
 1|SIMPLE     |js   |ALL   |journal_settings_unique,journal_settings_journal_id                                            |                                            |       |                                        |118  |Using where                                    |
 1|SIMPLE     |i    |ALL   |issues_journal_id                                                                              |                                            |       |                                        |379  |Using where; Using join buffer (flat, BNL join)|
 1|SIMPLE     |o0   |ref   |submission_search_object_keywords_unique,submission_search_object_keywords_keyword_id          |submission_search_object_keywords_keyword_id|8      |const                                   |58288|                                               |
 1|SIMPLE     |o    |eq_ref|PRIMARY,submission_search_objects_submission_id                                                |PRIMARY                                     |8      |ojs.o0.object_id                        |1    |                                               |
 1|SIMPLE     |s    |eq_ref|PRIMARY,submissions_context_id,submissions_publication_id                                      |PRIMARY                                     |8      |ojs.o.submission_id                     |1    |Using where                                    |
 1|SIMPLE     |ps   |eq_ref|publication_settings_unique,publication_settings_name_value,publication_settings_publication_id|publication_settings_unique                 |819    |ojs.s.current_publication_id,const,const|1    |Using index condition; Using where             |
 1|SIMPLE     |p    |eq_ref|PRIMARY                                                                                        |PRIMARY                                     |8      |ojs.s.current_publication_id            |1    |                                               |

And this is the EXPLAIN result without key:


id|select_type|table|type  |possible_keys                                                                                  |key                                         |key_len|ref                                     |rows|Extra                                                                        |
--+-----------+-----+------+-----------------------------------------------------------------------------------------------+--------------------------------------------+-------+----------------------------------------+----+-----------------------------------------------------------------------------+
 1|SIMPLE     |j    |const |PRIMARY                                                                                        |PRIMARY                                     |8      |const                                   |1   |Using temporary; Using filesort                                              |
 1|SIMPLE     |k0   |const |PRIMARY,submission_search_keyword_text                                                         |submission_search_keyword_text              |182    |const                                   |1   |Using index                                                                  |
 1|SIMPLE     |js   |ALL   |journal_settings_unique,journal_settings_journal_id                                            |                                            |       |                                        |118 |Using where                                                                  |
 1|SIMPLE     |i    |ALL   |issues_journal_id                                                                              |                                            |       |                                        |379 |Using where; Using join buffer (flat, BNL join)                              |
 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)|
 1|SIMPLE     |p    |eq_ref|PRIMARY                                                                                        |PRIMARY                                     |8      |ojs.s.current_publication_id            |1   |                                                                             |
 1|SIMPLE     |ps   |eq_ref|publication_settings_unique,publication_settings_name_value,publication_settings_publication_id|publication_settings_unique                 |819    |ojs.s.current_publication_id,const,const|1   |Using index condition; Using where                                           |
 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     |o0   |ref   |submission_search_object_keywords_unique,submission_search_object_keywords_keyword_id          |submission_search_object_keywords_keyword_id|16     |const,ojs.o.object_id                   |1   |Using index                                                                  |

Hi @liviagouvea,

Stripping out comparable rows with the same join strategy, you’re left with…

With primary key

 1|SIMPLE     |o    |eq_ref|PRIMARY,submission_search_objects_submission_id                                                |PRIMARY                                     |8      |ojs.o0.object_id                        |1    |                                               |
 1|SIMPLE     |s    |eq_ref|PRIMARY,submissions_context_id,submissions_publication_id                                      |PRIMARY                                     |8      |ojs.o.submission_id                     |1    |Using where                                    |

Without primary key

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

This is a little beyond my experience, but you might want to look into tweaking the optimizer configuration – see e.g.:

I’m using MariaDB 11.6.2, but I also tried MySQL latest and still slow.
I’ll take a look in these suggestions.
Thank you very much for your attention!

This topic was automatically closed after 9 days. New replies are no longer allowed.