Search users very slow

Hi, Greetings to all.

The search users it is very slow, by checked Include users with no roles in this journal. When i run in the ojs-3.1.2-1 the query return the result, follow the specifications on my server:
Application Version - e.g., OJS 3.3.0-6
Database version - MariaDB 10.3-27
Php version - 7.4
Captura de tela 2021-06-22 105925

Before you post your question, check out the user guides and video tutorials at PKP Docs and search the forum to see if someone else had the same question.

When you post your question give as much detail as possible, including the following:

  • Application Version - e.g., OJS 3.1.2
  • Description of issue
  • Steps you took leading up to the issue
  • What you tried to resolve the issue
  • Screenshots
  • Error log messages if applicable

You can post in any language, but please note there is an Español category, an Italiana category, a Deutsche category, and an external Spanish OJS forum.

Do not share any information that could compromise the security of your site or the privacy of your users.

How many users in total site-wide? We are having the same issue. but our OJS version is 3.3.0-6

There is a known performance issue in 3.1.2 and can be solved by adding an index to user_settings table.

Also check your DB and tables to be sure all of them are running the same engine (recommendation is innoDB).

If (as @mapress888 said) you discover you got a lot of spam users, this post would be useful to you:

Grants @mapress888,

But the addition of the index in the table, not resolution.

Hi @marc,

The tables it is the InnoDB engine.

I’ve read and used the method from this post. However, the current PHP tool mergeuser.php crashes a lot. - every few hundred or maybe 1-2 thousand I will need to find out where it stops and re-input the rest of the list into the tool. took me 8 hours to clean 20k. have to figure out another way to do it.

By saying 700k total user, the actual user of every journal is around 50k, with this number of users the users & roles still very slow to load. - from 4mins to never.

Is there a way to optimize the query? most of time we only stuck on this one:

select 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, MAX(ra.date_assigned) as last_assigned,
(SELECT SUM(CASE WHEN ra.date_completed IS NULL AND ra.declined <> 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as incomplete_count,
(SELECT SUM(CASE WHEN ra.date_completed IS NOT NULL AND ra.declined <> 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as complete_count,
(SELECT SUM(CASE WHEN ra.declined = 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as declined_count,
(SELECT SUM(CASE WHEN ra.cancelled = 1 THEN 1 ELSE 0 END) FROM review_assignments AS ra WHERE u.user_id = ra.reviewer_id) as cancelled_count,
AVG(DATEDIFF(ra.date_completed, ra.date_notified)) as average_time, (SELECT AVG(ra.quality) FROM review_assignments AS ra
WHERE u.user_id = ra.reviewer_id AND ra.quality IS NOT NULL) as reviewer_rating from users as u
left join user_user_groups as uug on uug.user_id = u.user_id
left join user_groups as ug on ug.user_group_id = uug.user_group_id
left join user_settings as ugl on ugl.user_id = u.user_id and ugl.setting_name = ‘givenName’ and ugl.locale = ‘en_US’
left join user_settings as ugpl on ugpl.user_id = u.user_id and ugpl.setting_name = ‘givenName’ and ugpl.locale = ‘en_US’
left join user_settings as ufl on ufl.user_id = u.user_id and ufl.setting_name = ‘familyName’ and ufl.locale = ‘en_US’
left join user_settings as ufpl on ufpl.user_id = u.user_id and ufpl.setting_name = ‘familyName’ and ufpl.locale = ‘en_US’
left join user_group_stage as ugs on uug.user_group_id = ugs.user_group_id left join user_settings as us on u.user_id = us.user_id
left join user_interests as ui on u.user_id = ui.user_id
left join controlled_vocab_entry_settings as cves on ui.controlled_vocab_entry_id = cves.controlled_vocab_entry_id
left join review_assignments as ra on u.user_id = ra.reviewer_id where ug.context_id = 2 and ug.role_id in (4096) and u.disabled = 0 and ugs.stage_id = 3
and (lower(u.username) LIKE ‘%or%’ or lower(u.email) LIKE ‘%or%’
or (us.setting_name = ‘givenName’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘familyName’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘affiliation’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘biography’ and lower(us.setting_value) LIKE ‘%or%’)
or (us.setting_name = ‘orcid’ and lower(us.setting_value) LIKE ‘%or%’)
or lower(cves.setting_value) LIKE ‘%or%’)
group by u.user_id, user_given, user_family order by u.user_id desc LIMIT 30 OFFSET 0

have you try: Performance issue using LIMIT and OFFSET in search users query · Issue #5288 · pkp/pkp-lib · GitHub

by this query:

CREATE INDEX user_settings_setting_name_index ON user_settings (setting_name);

We have OJS 3.3.0-5 and have the same problem here … specially when the “Include users with no roles in this journal” option is checked…

Our installation is a multijournal one, with around 5000 users, and is really common to share users among several journals… so searching for existing users with no role in a particular journal is also common

Enabling the debug in config.inc.php and checking the server logs seems to point this query as the problematic one (searching for username “pkrzywoszynski”):


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 = "es_ES") LEFT JOIN user_settings ugpl ON (u.user_id = ugpl.user_id AND ugpl.setting_name = "givenName" AND ugpl.locale = "es_ES") 
LEFT JOIN user_settings ufl ON (u.user_id = ufl.user_id AND ufl.setting_name = "familyName" AND ufl.locale = "es_ES") LEFT JOIN user_settings ufpl ON (u.user_id = ufpl.user_id AND ufpl.setting_name = "familyName" AND ufpl.locale = "es_ES") 
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 "%pkrzywoszynski%" OR LOWER(cves.setting_value) LIKE "%pkrzywoszynski%" )   ) 
ORDER BY user_family, user_given LIMIT 25 OFFSET 0 ;

Any advice to solve this issue would be very useful!

Best regards!