Search is slow and not always working after upgrade to OJS 3.2.1.1

Hello,

We use OJS 3.1.2.4.
Before upgrading definitely to OJS 3.2.1.1, we are testing this version.

We noticed the search function in search page doesn’t work the same between the 2 versions.

In OJS 3.2.1.1, when we enter keywords in the query field, results display more slowly in general than in OJS 3.1.2.4.
And in OJS 3.2.1.1 when the number of results is greater than 300, the search page can’t stop running and results never displayed!

There are nothing special in error logs file.

We are using Mysql 5.5 and PHP 7.3.

Do you have any idea what the problem could be ?

Thanks in advance for your answer.
Best regards.
Helene

1 Like

Hello,

I would like to give more details on my last message.
I have carried out numerous tests. It’s true the duration of the display seems longer in OJS 3.2.1.1. But I also made tests which may have disturbed the search function. In OJS 3.1.2.4 and earliest versions, if we put “***” as keyword in the query field, we could display all articles. In OJS 3.2.1.1, it seems to no longer works any more. The search page can’t stop running and results never displayed. And it’s too bad because we used this feature to add a menu page and display all articles with pagination. And after that, the search function seems blocked. Is there any way to display all articles as in previous versions ?

Thanks in advance for your answer.
Best regards.
Helene

1 Like

Hi @hcl,

Here’s a github entry for an issue relating to slow search performance; there are a couple of SQL statements that you can run there that should improve the performance considerably. Please start with that, and let me know from there how it’s working.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,
Adding indexes solved the problems!
Thanks a lot.
Kind regards.
Helene

1 Like

Hello again @asmecher,

Unfortunately, I spoke too quickly, in OJS 3.2.1.1, finding all articles by entering three stars “***” still doesn’t work.
The search page can’t stop running and results never displayed.

I tested on this demo website:

On the demo website, search with three stars works but I don’t know in which version of OJS3 it is.

Are you having the same search problem with OJS 3.2.1 when searching with three stars to find all articles?
I don’t know if it’s a problem of Database or if it’s when submitting the form that does not accept anymore the special characters three stars “***”.

Thanks again for your help.
Best regards.
Helene

Hi,

To add some informations, in SQL request the three stars ‘***’ are changed in ‘%%%’.

In our database,

in OJS 3.1.2.4 this request works:

SELECT o.submission_id, MAX(s.context_id) AS journal_id, MAX(i.date_published) AS i_pub, MAX(ps.date_published) AS s_pub, COUNT(*) AS count
FROM submissions s, published_submissions ps, issues i, submission_search_objects o
NATURAL JOIN submission_search_object_keywords o0
NATURAL JOIN submission_search_keyword_list k0
WHERE s.submission_id = o.submission_id
AND ps.submission_id = s.submission_id AND i.issue_id = ps.issue_id
AND s.status = 3 AND i.published = 1
AND k0.keyword_text LIKE ‘%%%’ AND i.journal_id = 1
GROUP BY o.submission_id ORDER BY count DESC LIMIT 1500;

in OJS 3.2.1.1 this request doesn’t work:

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 
NATURAL JOIN submission_search_object_keywords o0 
NATURAL JOIN submission_search_keyword_list k0 
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') 
JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value) 
JOIN submission_search_objects o ON (s.submission_id = o.submission_id) 
WHERE s.status = 3 AND i.published = 1 
AND k0.keyword_text LIKE '%%%' AND i.journal_id = 1 
GROUP BY o.submission_id ORDER BY count DESC LIMIT 1500;

I added those indexes:

CREATE INDEX submission_search_object_submission ON submission_search_objects (submission_id);
CREATE INDEX submissions_publication_id ON submissions (current_publication_id);

Maybe it’s not enough.

Thanks again for your help.
Kind regards.
Helene

Hi @hcl,

As far as I’m aware, there has never been special handling of *** as a way to query all submissions; is this in the documentation somewhere?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

You absolutely right, there has never been special handling of *** as a way to query all submissions; This is not in the documentation. But we discovered it was working for all versions of OJS we used until 3.1.2.4 and it was very useful.
It probably means you don’t have to maintain this feature.
I will still try to keep looking why the query is not working. It could be interesting to know.
And even if it’s not an official feature of OJS, someone could use it and generate the query which never ends and crash the database.

Best regards
Helene

Hi @asmecher, Hi all,

Here is the result of my first tests.
As a reminder, the original SQL query in OJS 3.2.1.1 I used is as follows:

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
NATURAL JOIN submission_search_object_keywords o0
NATURAL JOIN submission_search_keyword_list k0
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’)
JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)
JOIN submission_search_objects o ON (s.submission_id = o.submission_id)
WHERE s.status = 3 AND i.published = 1
AND k0.keyword_text LIKE ‘[keyword]
AND i.journal_id = 1
GROUP BY o.submission_id ORDER BY count DESC LIMIT 1500;

Test 1:
k0.keyword_text LIKE '%red%'

+ the original query
714 rows in set (51.04 sec)

Test 2:
k0.keyword_text LIKE '%red%'

+ by replacing “JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)”
with "JOIN issues i ON (i.issue_id = ps.setting_value)"

714 rows in set (0.36 sec)

Test 3:
k0.keyword_text LIKE '%%%'

+ the original query
Run for ever and never ends…

Test 4:
k0.keyword_text LIKE '%%%'

+ by replacing “JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)”
with "JOIN issues i ON (i.issue_id = ps.setting_value)"

1317 rows in set (35.10 sec)

Test 5:
k0.keyword_text LIKE '%%%'
+ Test 4 + adding "AND i.journal_id=s.context_id"
1317 rows in set (17.52 sec)

Test 6:
k0.keyword_text LIKE '%%%'
+ Test 4 + Test 5 + adding " CREATE INDEX publication_settings_id_name ON publication_settings (publication_id,setting_name)"
1317 rows in set (1.61 sec)

In summary here is the 3 things which have improved the response time of queries that contain “%”:

  • Replacing “JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)” with “JOIN issues i ON (i.issue_id = ps.setting_value)”
  • Adding “AND i.journal_id=s.context_id”
  • Adding Index: “CREATE INDEX publication_settings_id_name ON publication_settings (publication_id,setting_name);”

You could test those things in your environment and let me know if you think it can improve research function and be added in a next release of OJS.

Best regards.
Helene

Hi @hcl,

Thanks for digging into this – I’ll work through your suggestions one at a time:

  • #1 (removing numeric to string cast): Unfortunately this will cause problems in PostgreSQL, as it doesn’t like to implicitly convert between strings and numbers. Some further experimentation here might find a good alternative.
  • #2 (adding a join on journal ID): Could you propose this as a Github pull request?
  • #3 (creating an index): Alternatively, I think adding a condition AND ps.locale IS NULL would cause the existing UNIQUE index to be used. If you can confirm this, and propose the change as a Github pull request, I can look it over.

This kind of investigation is much appreciated!

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

I run some other tests:

Replacing :
“JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)” with “JOIN issues i ON (i.issue_id = ps.setting_value)”
Has approximately the same effect than :
Replacing “JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)” with “JOIN issues i ON (i.issue_id = CAST(ps.setting_value AS UNSIGNED))”

If PostgreSQL doesn’t like to implicitly convert between strings and numbers, it could be a solution to reverse CAST to make happy both Mysql and PostgreSQL

I confirm adding a join on journal ID reduce time of response.
Besides, this join exists in a similar query in “getBySubmissionId function” in IssueDAO.inc.php file.
I will suggest this as a Github pull request.

Concerning the final test you suggest: adding a condition such as “AND ps.locale IS NULL” to ensure that the existing UNIQUE index to be used.
Yes it reduces time of approximately 20 seconds. I don’t know if it’s enough significant.
But in our Mysql “AND ps.locale IS NULL” doens’t work, I have to replace it by this condition: “AND ps.locale=’’”
Do you think I can still propose this change as a Github pull request?

In summary for condition k0.keyword_text LIKE ‘%%%’*

In original query: Run for ever, never ends

With adding the 3 conditions: 1317 rows in set (1.64 sec)

  • JOIN issues i ON (i.issue_id = CAST(ps.setting_value AS UNSIGNED))
  • AND i.journal_id=s.context_id
  • AND ps.locale=’’

Best regards.
Helene

Hi all,

The discussion continues over here! https://github.com/pkp/pkp-lib/issues/6321

Regards,
Alec Smecher
Public Knowledge Project Team

If not done yet, I would like to suggest that every table gets a primary key. This is necessary that OJS can be run on a MariaDB cluster.

These are the tables in a OJS-3.2.1-2 database that don’t have a primary key:

MariaDB [ojs3test]> SELECT t.TABLE_SCHEMA, t.TABLE_NAME
    -> FROM information_schema.TABLES AS t
    -> LEFT JOIN information_schema.KEY_COLUMN_USAGE AS c 
    -> ON t.TABLE_SCHEMA = c.CONSTRAINT_SCHEMA
    ->    AND t.TABLE_NAME = c.TABLE_NAME
    ->    AND c.CONSTRAINT_NAME = 'PRIMARY'
    -> WHERE t.TABLE_SCHEMA != 'information_schema'
    ->    AND t.TABLE_SCHEMA != 'performance_schema'
    ->    AND t.TABLE_SCHEMA != 'mysql'
    ->    AND c.CONSTRAINT_NAME IS NULL;
+--------------+------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME                               |
+--------------+------------------------------------------+
| ojs3test     | filter_settings                          |
| ojs3test     | author_settings                          |
| ojs3test     | scheduled_tasks                          |
| ojs3test     | user_interests                           |
| ojs3test     | site                                     |
| ojs3test     | review_round_files                       |
| ojs3test     | external_feed_settings                   |
| ojs3test     | plugin_settings                          |
| ojs3test     | static_page_settings                     |
| ojs3test     | submission_settings                      |
| ojs3test     | books_for_review_settings                |
| ojs3test     | item_views                               |
| ojs3test     | group_settings                           |
| ojs3test     | user_group_settings                      |
| ojs3test     | email_log_users                          |
| ojs3test     | submission_file_settings                 |
| ojs3test     | query_participants                       |
| ojs3test     | sessions                                 |
| ojs3test     | section_settings                         |
| ojs3test     | review_files                             |
| ojs3test     | user_user_groups                         |
| ojs3test     | issue_settings                           |
| ojs3test     | group_memberships                        |
| ojs3test     | event_log_settings                       |
| ojs3test     | genre_settings                           |
| ojs3test     | journal_settings                         |
| ojs3test     | site_settings                            |
| ojs3test     | processes                                |
| ojs3test     | custom_issue_orders                      |
| ojs3test     | custom_section_orders                    |
| ojs3test     | review_form_settings                     |
| ojs3test     | user_group_stage                         |
| ojs3test     | library_file_settings                    |
| ojs3test     | metrics                                  |
| ojs3test     | object_for_review_settings               |
| ojs3test     | roles                                    |
| ojs3test     | navigation_menu_item_settings            |
| ojs3test     | issue_galley_settings                    |
| ojs3test     | notification_settings                    |
| ojs3test     | review_form_responses                    |
| ojs3test     | referral_settings                        |
| ojs3test     | citation_settings                        |
| ojs3test     | category_settings                        |
| ojs3test     | review_object_metadata_settings          |
| ojs3test     | review_object_type_settings              |
| ojs3test     | usage_stats_temporary_records            |
| ojs3test     | versions                                 |
| ojs3test     | metadata_description_settings            |
| ojs3test     | announcement_settings                    |
| ojs3test     | review_form_element_settings             |
| ojs3test     | publication_galley_settings              |
| ojs3test     | subscription_type_settings               |
| ojs3test     | email_templates_settings                 |
| ojs3test     | subeditor_submission_group               |
| ojs3test     | oai_resumption_tokens                    |
| ojs3test     | announcement_type_settings               |
| ojs3test     | controlled_vocab_entry_settings          |
| ojs3test     | user_settings                            |
| ojs3test     | publication_settings                     |
| ojs3test     | publication_categories                   |
| ojs3test     | email_templates_default_data             |
| ojs3test     | submission_search_object_keywords        |
| ojs3test     | data_object_tombstone_settings           |
| ojs3test     | navigation_menu_item_assignment_settings |
+--------------+------------------------------------------+

@asmecher - shall I open a separate issue on GitHub with the tables above?

Hi @mpbraendle,

There’s an old issue open about this already: https://github.com/pkp/pkp-lib/issues/3573

Starting with OJS 3.3 we’re using a new toolset (Laravel’s) to manage our database schema, so it might be a good time to revisit that issue. Please feel free to drop your feedback onto the end of that discussion, and I’ll update it with the latest progress on our side.

Thanks,
Alec Smecher
Public Knowledge Project Team

1 Like