User search query take a long time

Hi i used ojs 3.1.2.2
When search a name on the user list grade, OJS run a big query with a lot of left joins, that takes a long time to run

SELECT COUNT() FROM (SELECT DISTINCT u., COALESCE(ugl.setting_value, ugpl.setting_value) AS user_given, CASE WHEN ugl.setting_value <> ‘’ THEN ufl.setting_value ELSE ufpl.setting_value END AS user_family FROM users AS u LEFT JOIN user_settings us ON (us.user_id = u.user_id AND us.setting_name = ‘affiliation’) LEFT JOIN user_interests ui ON (u.user_id = ui.user_id) LEFT JOIN controlled_vocab_entry_settings cves ON (ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id) LEFT JOIN user_user_groups uug ON (uug.user_id = u.user_id) LEFT JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id) LEFT JOIN user_settings ugl ON (u.user_id = ugl.user_id AND ugl.setting_name = ‘givenName’ AND ugl.locale = ‘fa_IR’) LEFT JOIN user_settings ugpl ON (u.user_id = ugpl.user_id AND ugpl.setting_name = ‘givenName’ AND ugpl.locale = ‘fa_IR’) LEFT JOIN user_settings ufl ON (u.user_id = ufl.user_id AND ufl.setting_name = ‘familyName’ AND ufl.locale = ‘fa_IR’) LEFT JOIN user_settings ufpl ON (u.user_id = ufpl.user_id AND ufpl.setting_name = ‘familyName’ AND ufpl.locale = ‘fa_IR’) LEFT JOIN user_settings usgs ON (usgs.user_id = u.user_id AND usgs.setting_name = ‘givenName’) LEFT JOIN user_settings usfs ON (usfs.user_id = u.user_id AND usfs.setting_name = ‘familyName’) WHERE 1=1 AND ( ( LOWER(CONCAT(COALESCE(usgs.setting_value,’’),COALESCE(usfs.setting_value,’’),u.email,COALESCE(us.setting_value,’’))) LIKE ‘%zarg%’ OR LOWER(cves.setting_value) LIKE ‘%zarg%’ ) ))

How can i update this query to optimize version
Thanks in advanced

Hi @mbabaei,

Would you be willing to provide me with a copy of your database for testing purposes?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher

As you know i am developer of OJS at a big university, and we have 52 active journals on OJS, for sending database i need to get permit from the collection managers and i think it is very hard but i have another suggestion if you want i can share with you USER and USER_SETTINGS table
Maybe it is better for me

Thanks in advance

Hi @mbabaei,

Alternately, this issue may have inadvertently solved the problem: https://github.com/pkp/pkp-lib/issues/5269

Meanwhile, I’ll ask around about a big database to experiment with. But I’d probably need more than the users and user_settings tables.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher
Please check your private messages.
Thanks

Hi @mbabaei,

Looking into this query a little further I was able to optimize some things, but there’s a fundamental part we can’t fix with simple changes. The search looks in the first name, last name, and affiliation entries (all localized), plus email address and reviewer interests. As the database grows, and because these searches aren’t anchored e.g. to the beginning of any of these fields, they aren’t well-indexed and don’t perform well.

Both MySQL and PostgreSQL support full-text indexes, so it’s possible we could use those, but before I look into that it would be helpful to learn more about the uses you have for this form. Can you describe what you’re doing in the workflow when you run into poorly-performing searches?

(For example, it might be possible to improve the performance if you’re searching based on the start of a name or email address; or if searching itself isn’t important, maybe we can offer a different workflow.)

Thanks,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

As you reviewed this query, i think there is a big bug on this part because when it run, there is not any condition to optimize this query i mean we can do it as below:

// Include users
	if (!is_null($this->includeUsers)) {
		$includeUsers = $this->includeUsers;
		$q->orWhereIn('u.user_id', $includeUsers);
	}

In Our system user trying to find a user with user given name or family, and for another recommendation we can put a search option
Screenshot%20from%202019-12-25%2009-59-55

Hi @mbabaei,

I’m not sure I’m following you on that first part – can you describe it in more detail?

Thanks,
Alec Smecher
Public Knowledge Project Team

@asmecher,

We should check what is the search word type? Given name or family or affilation
If you check the query and the result you can see that 4 left join is west join and never used.

Thank you very much bro
Merry Christmas

Hi @mbabaei,

Yes, that is a possibility, but even a savings of 6 or so joins might not perform sufficiently if it still requires a complete scan of the remaining join or two. Can you describe the tasks you’re performing when you use the full search? For example, do you use this because a user has registered but needs to be assigned a role in the journal? Or needs to have some element of their account changed by a journal manager?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher

Yes that is true i used this section for finding a special user and edit the information or check is it active or not! for another task i send activation mail again for user that does not receive activation mail or …

I think if you add a select (drop down) to user select what type of information entered and set minimum 3 or 4 word letter for starting search process is the good idea

This github issue appears to be related: https://github.com/pkp/pkp-lib/issues/5288

Is there any solution for this problem? We have over 25000 users, and searching is almost imposible
We are using ojs 3.1.2

Hi @Dragoljub_Djordjevic,

I haven’t had time to work on optimizing that yet – but are your accounts valid or are you getting spam registrations?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher :slight_smile:

Most of them are valid. We have over 100 journals, and database is 10 years old.

Biggest problem is when journal manager tries to add already registered user to his journal. Search query takes couple of minutes to finish, or even reaches timeout.

Also, when searching all users, we can’t search by role. for example, I want to find all users registered as reviewers.

Hi @Dragoljub_Djordjevic,

That query could definitely use optimization, as the numbers of rows aren’t enormous. I’ve scheduled that issue against OJS 3.2.1 and will try to prioritize it. Of course, we’d welcome third-party work on this as well, if you have the time and resources.

Thanks,
Alec Smecher
Public Knowledge Project Team

Thanks. Can you paste here a link to github page?

Hi @Dragoljub_Djordjevic,

It’s just above, in this same topic.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi all,

If anyone is able to try creating an index that may resolve the issue, please have a look at https://github.com/pkp/pkp-lib/issues/5288#issuecomment-599763452 – your confirmation would be helpful!

Thanks,
Alec Smecher
Public Knowledge Project Team