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
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:
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
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!
A tip for this case is to reduce the default item list value on Configuration > Website > Setup > Lists. Even with a not thousand users on database, if this field is blank, system will load all users on list and it turns the loading process very slow.
It is important to keep users table clean, but many times it is possible to have a fast loading on users list with a max value on 10-30 items.
When the system is upgraded from 2.x to 3.x version, this field can be blank and this issue can happen.