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
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
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
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
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.
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
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
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.
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
